Excel 2010 - INDEX/MATCH w/Array Search
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
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_POWE
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
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
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_POWE
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
ASKER
Indeed...typo on my part.
This works brilliantly!
Can you explain the "9.9999E+307" bit? Why this value?
This works brilliantly!
Can you explain the "9.9999E+307" bit? Why this value?
ASKER
Quick and accurate! Thanks - Mark
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I did notice that as I added it to my production sheet. Thanks for the revised formula. I'll test it shortly.
ASKER
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.
I am going to request that the question be re-opened so that I can accept your 2nd answer.
=IFERROR(LOOKUP(9.9999E+30
note: Should Sheet1!B2 be LNGTH so that it would match Sheet2, column A data... please check.