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

[brolsen]

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!

 

[mikerickson]

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

 

[brolsen]

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?

 

[brolsen]

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

 

[HAZELLBOO]

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]

If you have a more recent version of Excel, you could use the IFERROR function.

 

[Aladin Akyurek]

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]

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]

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]

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:

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

Markmzz