Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel 2010 - INDEX/MATCH w/Array Search

Posted on 2013-06-18
7
Medium Priority
?
720 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 23

Accepted Solution

by:
NBVC earned 2000 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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

The SignAloud Glove is capable of translating American Sign Language signs into text and audio.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

604 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