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!
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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:
Upvote 0
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?
 
Upvote 0
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).
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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
Back
Top