excel - If string contains word from list, return value adjacent to list value

Question

Currently I have a set of cells, and each has, among useless information, a unique identifier. I also have a list of these unique identifiers, as well as what value each identifier corresponds to.

What I would like to do is find which, if any, identifier a cell contains, and then output the corresponding value, below is an example:

http://i.stack.imgur.com/97aKI.png

So where the cell contains "ADC", I would like excel to find where ADC comes up in the reference array, and then return the corresponding value.

If this can be done with a formula or a macro, either would be great. I have tried fiddling with index, match, and search, in various combinations, but nothing seems to be working. I have found creating a massive if statement to be impractical as there are about 70 unique values to search for.

Any suggestions would be welcome!

edit: I was recommended to use vlookup, but I am not looking for an identical match, but instead for a specific value contained within a string. If vlookup does have this functionality then could somebody show me how to put this into practice with my specific example?

Answer 1

One method of a 'reverse-wildcard' lookup can be achieved is with the newer AGGREGATE1 function. This function can produce cyclic calculation and has an option (e.g. 6) to discard errors. Use this to produce a row number on the match to the cross-reference table with the INDEX function returning the actual value.

??????

The formula in B3 is,

=INDEX(F$3:F$5, AGGREGATE(15, 6, ROW($1:$3)*SIGN(MATCH("*"&E$3:E$5&"*", A3, 0)), 1))

Note that ROW(1:3) is the position within F3:F5, not the actual row number on the worksheet. I've also scrambled the Find and Insert values in your original cross-reference table to avoid the perception of an associative lookup match.

---

1 _{The AGGREGATE function was introduced with Excel 2010. It is not available in earlier versions.}