Solved

Excel 2010 - INDEX/MATCH w/Array Search

Posted on 2013-06-18
7
697 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article is meant to give a basic understanding of how to use R Sweave as a way to merge LaTeX and R code seamlessly into one presentable document.
This article will show, step by step, how to integrate R code into a R Sweave document
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

706 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now