Solved

Excel 2010 - INDEX/MATCH w/Array Search

Posted on 2013-06-18
7
710 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying 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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
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.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

724 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