Search cell text and then display text for multiple possibilities ??

[XLS888]

Hi,

In B1 I have the following formula to search A1. Similarly, B2 to search A2, etc.

=IF(ISNUMBER(SEARCH("COSTCO",A1)), "Supermarket","")

However, I need to have multiple criteria all within the above formula. For example:

IF "COSTCO" then display "Supermarket"

IF "TESCO" then display "Supermarket"​

IF "LLOYDS" then display "Bank"

IF "BARCLAYS" then display "Bank"​

IF "VODAFONE" then display "Mobile"
... etc for about 20 items.


And if none of the above, then display nothing. Presumably ""


Secondly, feel free to say if there if there is a better way (though shorter formulae preferred) to achieve this other than

=IF(ISNUMBER(SEARCH​

​

Thanks!

 

[Peter_SSs]

Would this work for you?
I have made a lookup table in columns E:F. Those columns could be hidden or placed on another worksheet.
Formula in B2 is copied down after adjusting ranges to suit your full list.

Excel Workbook
	A	B	C	D	E	F
1
2	Delivered to Costco	Supermarket			COSTCO	Supermarket
3	Barclays in High Street	Bank			TESCO	Supermarket
4					LLOYDS	Bank
5	That is a red car				BARCLAYS	Bank
6	Which Tesco dod you go to?	Supermarket			VODAFONE	Mobile
7	Vodafone London	Mobile
XLS888

 

[XLS888]

Brilliant, thanks.

On reflection it is easier to have a separate table rather trying to make a very long formula. Cheers

 

[Peter_SSs]

Brilliant, thanks.

On reflection it is easier to have a separate table rather trying to make a very long formula. Cheers

You're welcome. Yes, it would be a very long formula without a table if you have 20+ options!