Solved

Excel 2010 - INDEX/MATCH w/Array Search

Posted on 2013-06-18
7
700 Views
Last Modified: 2013-06-19
Hoping for some quick help on a time-sensitive project...

I have attached a spreadsheet that illustrates what I'm trying to do.  On Sheet1, I have a list of groups in column A.  In columns B-E, there are one or more "terms" associated with each group from column A.

Example:  ATTR_GROUP_PROD_DATA_POWER  ~ VOLT / AMP / HP / HORSEPOWER

On Sheet 2, I have a list of text values that may contain one of the "terms' from Sheet 1.  What i need is a formula or routine to return the Attribute Group from Sheet1, depending on which row the term match is made.

Example: for attribute, "ATTR_INCOMING_VOLTAGE", the formula should return the value of "ATTR_GROUP_PROD_DATA_POWER" because "VOLTAGE" is found in table row 12.

I've tried building an array formula using INDEX/MATCH, but wasn't able to figure out how to search the array of search terms against the attribute name.

I'd prefer a Formula solution, but VBA is workable.
ARRAY-SEARCH.xlsx
0
Comment
Question by:mark_harris231
  • 4
  • 2
7 Comments
 
LVL 23

Expert Comment

by:NBVC
ID: 39257633
Try:

=IFERROR(LOOKUP(9.9999E+307,SEARCH(Sheet1!$B$2:$E$15,A2),Sheet1!$A$2:$A$15),"")


note:  Should Sheet1!B2 be LNGTH so that it would match Sheet2, column A data... please check.
0
 
LVL 10

Author Comment

by:mark_harris231
ID: 39257653
Indeed...typo on my part.

This works brilliantly!

Can you explain the "9.9999E+307" bit?  Why this value?
0
 
LVL 10

Author Comment

by:mark_harris231
ID: 39257658
Quick and accurate!   Thanks - Mark
0
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 23

Accepted Solution

by:
NBVC earned 500 total points
ID: 39257721
Actually, upon looking this over again... I might have mislead you with that formula... it only works if the lookup values are in column B of Sheet1....  for example if you use AMPERAGE in you sample above instead of VOLTAGE, then it doesn't work....

Try instead:

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH("_"&Sheet1!$B$2:$E$15&"_","_"&A2&"_")))),INDEX(Sheet1!$A$2:$A$15,SUMPRODUCT(--(ISNUMBER(SEARCH("_"&Sheet1!$B$2:$E$15&"_","_"&A2&"_")))*(ROW(Sheet1!$B$2:$E$15)-ROW(Sheet1!$B$2)+1))),"")
0
 
LVL 10

Author Comment

by:mark_harris231
ID: 39257835
I did notice that as I added it to my production sheet.  Thanks for the revised formula.  I'll test it shortly.
0
 
LVL 10

Author Comment

by:mark_harris231
ID: 39257855
Works a treat!  Thanks for the 2nd look.

I am going to request that the question be re-opened so that I can accept your 2nd answer.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question