CODEWITHSUNDEEP
excelif-statementsearchvlookupcriteria
Jonathan Marin
Jonathan Marin

Reputation: 21

Searching for multiple words and using IF/OR/Vlookup function

I'm trying to create a formula that will search a cell for the following words.. "Other" & "Repair" if either of these words are found I want it to be categorized as the word that is found. If none of these words are found I want the formula to Vlookup another column to then categorize it.

I got the formula to work for one search word, I cant figure out how to do it with two search words.

below is the formula I used for one word search criteria. =IF(ISNUMBER(SEARCH("REPAIR",B9089)),"REPAIR",VLOOKUP(E9089,Key!$D:$E,2,0))

This is what I tried doing for the two search words but it breaks at the end for the true / flase statement

=IF(OR(ISNUMBER(SEARCH("REPAIR",B9090)),ISNUMBER(SEARCH("OTHER",B9090))),"REPAIR""OTHER",VLOOKUP(E9090,Key!$D:$E,2,0))

Upvotes: 1

Views: 1196

Answers (2)

riskypenguin
riskypenguin

Reputation: 2199

I think you'll need nested IF statements, like this:

=IF(ISNUMBER(SEARCH("REPAIR",B9089)),"REPAIR", IF(ISNUMBER(SEARCH("OTHER",B9089)),"OTHER",VLOOKUP(E9089,Key!$D:$E,2,0))

What this does is first check if "REPAIR" is part of the speciified cell's value, if it isn't it checks if the cell value contains "OTHER", and if that is not the case it performs the desired VLOOKUP. Please not that this is doable for two conditions, but if it starts getting to higher numbers you should consider writing a custom function in VBA.

Upvotes: 0

Plutian
Plutian

Reputation: 2309

If you need to search for two values and the values returned, an OR statement will not work. Since it will only return true or false to an IF statement, and the IF will then return only one value. Instead, you can nest two IF statements inside each other for each of the values you need to find. Try the following formula:

=IF(ISNUMBER(SEARCH("Repair",B9089)),"Repair",IF(ISNUMBER(SEARCH("Other",B9089)),"Other",VLOOKUP(E9089,Key!$D:$E,2,0)))

(I am assuming your references and the Vlookup statement are correctly written by yourself)

Upvotes: 1

Related Questions