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