If contains text statements that return text string

**timbo86** · 10-29-2014, 06:24 AM

I want to create a formula that will check a text string in one cell and then return the relevant label in the row where the formula is entered.
So, for example, if a cell contains the text 'chicken' then 'chicken' is returned in the formula cell - but i need to do this for about 10 - 15 different matches against 1 column.

So if it contains 'chicken', then return 'chicken', if it contains 'apple' then return 'apple', if it contains 'pear' then return 'pear' etc.

I have tried to do this with a nested if/search statement but it would only match two different criteria for it stopped working.

Help!

Thanks

**Olly** · 10-29-2014, 06:47 AM

You could use a small UDF:

Please Login or Register  to view this content.

Use as

Formula:

Please Login or Register  to view this content.

The first argument = a comma separated list of values you want to check for
The second argument = the text to check
The (optional) third argument = set TRUE to ignore case, FALSE (or omitted) to match case.

**:) Sixthsense :)** · 10-29-2014, 06:59 AM

	A
1	Abc xyz
2	xyz Chicken
3	Def IJK
4	Lef Apple def
5	yjk lek pear

	D
1	Chicken
2	Apple
3	Pear

In B1 Cell

=IFERROR(LOOKUP(2,1/SEARCH($D$1:$D$3,A1),$D$1:$D$3),"Not Found In List")

Drag it down...

	B
1	=IFERROR(LOOKUP(2,1/SEARCH($D$1:$D$3,A1),$D$1:$D$3),"Not Found In List")

	B
1	Not Found In List
2	Chicken
3	Not Found In List
4	Apple
5	Pear

**timbo86** · 10-29-2014, 10:46 AM

Thanks Olly, that is really useful - but unfortunately I reached a character count limit for the query that I need. Is there a way to extend it?

6thsense - I tried your formula but couldn't get it to work, probably need a bit more of a description of what it is doing to understand it fully. I was thinking that a lookup could be good, but i'd like to do it from a separate sheet with all of the required fields, how would I do that?

Thanks