Solved

Excel 2010 - INDEX/MATCH w/Array Search

Posted on 2013-06-18
7
698 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Consolidate xl 2010 worksheets with text 2 24
Office 2016 Excel Issue 4 26
Excel - find text within text? 1 24
How do I crate a Pivot table in Excel 2 10
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

920 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

16 Experts available now in Live!

Get 1:1 Help Now