Search for multiple Values

[mdjks]

Each month I get a list of hotels people have used and need to normalize them by chain to run additional calculations. I have attached a sample. I had high hopes that a nested search function would work but

"=IF(SEARCH($G$2,A2)>0,$H$2,IF(SEARCH($G$3,A2)>0,$H$3,IF(SEARCH($G$4,A2)>0,$H$4,"not found")))"

only works when the first value is true. This formula would also get extremely long as there are over 40 search term/Chain result combinations. I am using Excel 2010 but saved the sample as 2003.

Thank you in advance for any suggestions.

 

[NBVC]

Try:

=LOOKUP(9.99999E+307,SEARCH($G$2:$G$4,A2),$H$2:$H$4)

copied down.

 

[mdjks]

not clear on what 9.99999E+307 was supposed to be.

 

[NBVC]

It represents a very large number, the largest excel can work with...

LOOKUP() looks for the last entry in a given range/vector that is smaller than or equal to the lookup value...

So the SEARCH() function will return an array of numbers (representing position within string that a searched item is found) and errors (where search items are not found).

Lookup ignores the errors and tries to find that large number and obviously does not find it so then it matches up to the last number in that array of position numbers and returns the coinciding items from the result vector.

 

[mdjks]

Thank you, giant help to us!