Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Excel 2010 - INDEX/MATCH w/Array Search

Posted on 2013-06-18
Medium Priority
733 Views
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
Question by:mark_harris231
• 4
• 2

LVL 23

Expert Comment

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

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

ID: 39257658
Quick and accurate!   Thanks - Mark
0

LVL 23

Accepted Solution

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....

=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

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

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

Question has a verified solution.

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

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This article will show how Aten was able to supply easy management and control for Artear's video walls and wide range display configurations of their newsroom.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
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…
###### Suggested Courses
Course of the Month13 days, 15 hours left to enroll

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

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