If cell contains (multiple text criteria) then return (corresponding text criteria)

brolsen

New Member
Joined
Aug 11, 2011
Messages
14
Hello All,


I am trying to have a formula that will check whatever is in a cell, if it is one multiple criteria, then I want it to return something else each time it appears.

So if I have 5 cells:
Dog Barks
Cat Purrs
Mouse Squeaks
Cat Purrs
Dog Barks

The left column contains one of several different words, I want to have it return a value in text (like in green above) depending on the variable in the first column.

I know:
=IF(ISNUMBER(SEARCH("Dog*",A1)),"Barks","")
Would work for the first one, but I am trying to figure out out to get it to move to the next one if the cell is not "Dog"? I need column A to be dynamic so if I changed the first one to "Cat," it would return "Purrs" in the second column.

Thanks in advance if you can figure this out for me!
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

brolsen

New Member
Joined
Aug 11, 2011
Messages
14
No that's not working for some reason, this is what I am getting with that formula:
Dog Purrs
Cat Purrs
Mouse Squeaks
Cat Purrs
Dog Purrs
Dog Purrs
Mouse Squeaks
Dog Purrs


Any ideas on why it works sometimes and not others? It seems completely random. It seems kinda weird because if I change any if the "dogs" to "cat" or "Mouse" it will change, but it never shows Barks.

Any clue?
 
Last edited:

HAZELLBOO

New Member
Joined
Mar 13, 2013
Messages
9

ADVERTISEMENT

How about
=LOOKUP(A1, {"Cat","Dog","Mouse"}, {"Purrs","Barks","Squeaks"})

Thank you! How about if I need to say if there is anything else, do this? I tried just adding a value at the end of the formula, but it didn't work.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,916
If you have a more recent version of Excel, you could use the IFERROR function.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

How about
=LOOKUP(A1, {"Cat","Dog","Mouse"}, {"Purrs","Barks","Squeaks"})

No that's not working for some reason, this is what I am getting with that formula:
Dog Purrs
Cat Purrs
Mouse Squeaks
Cat Purrs
Dog Purrs
Dog Purrs
Mouse Squeaks
Dog Purrs


Any ideas on why it works sometimes and not others? It seems completely random. It seems kinda weird because if I change any if the "dogs" to "cat" or "Mouse" it will change, but it never shows Barks.

Any clue?

Nevermind, I just had Dog and cat in different orders. Thanks!!!

Why do we invoke LOOKUP here instead of something like

=VLOOKUP(A1, {"Cat","Purrs";"Dog","Barks";"Mouse","Squeaks"},2,0)

which would be appropriate to the data?
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,916
Its been a while, but my guess is that the syntax for the LOOKUP is more straight forward than than for VLOOKUP. You don't have to be fussy about ; vs ,

The VLOOKUP is better for a large number of options (easier to convert to worksheet rather than explicit arrays).
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Its been a while, but my guess is that the syntax for the LOOKUP is more straight forward than than for VLOOKUP. You don't have to be fussy about ; vs ,

The VLOOKUP is better for a large number of options (easier to convert to worksheet rather than explicit arrays).

i meant rather to point out the risk that one would run with LOOKUP, for example, when A1 = Donkey, etc.
 

markmzz

MrExcel MVP
Joined
May 7, 2011
Messages
3,699
Hello All,


I am trying to have a formula that will check whatever is in a cell, if it is one multiple criteria, then I want it to return something else each time it appears.

So if I have 5 cells:
Dog Barks
Cat Purrs
Mouse Squeaks
Cat Purrs
Dog Barks

The left column contains one of several different words, I want to have it return a value in text (like in green above) depending on the variable in the first column.

I know:
=IF(ISNUMBER(SEARCH("Dog*",A1)),"Barks","")
Would work for the first one, but I am trying to figure out out to get it to move to the next one if the cell is not "Dog"? I need column A to be dynamic so if I changed the first one to "Cat," it would return "Purrs" in the second column.

Thanks in advance if you can figure this out for me!

Another way:

Code:
=IF(COUNT(SEARCH({"Dog";"Cat";"Mouse"},A1)),MID(A1,FIND(" ",A1)+1,100),"")


Markmzz
 

Watch MrExcel Video

Forum statistics

Threads
1,122,976
Messages
5,599,143
Members
414,292
Latest member
kingshuk963

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top