SOLVED

Refining an If statement

Copper Contributor

Table 1.col A has a list of comments. Col B I currently have an if statement to pick out recurring words to classify the comment. I want to refine this to make it more manageable. Any suggestions.
=IF(ISNUMBER(SEARCH("Luton",B2)),"Van",IF(ISNUMBER(SEARCH("Mini",B2)),"Car",IF(ISNUMBER(SEARCH("Canoe",B2)),"Boat",IF(ISNUMBER(SEARCH("Mitsubishi",B2)),"Car"))))

5 Replies

@Bridget_T 

If you create a Lookup Table like this... (in this case the Lookup Table is created in the range R1:S4)

Lookup Table.jpg

 

Then you can simply use this formula...

=INDEX($S$1:$S$4,MATCH(TRUE,ISNUMBER(SEARCH($R$1:$R$4,B2)),0))

 

Just change the ranges referred in the above formula based on the range of your Lookup Table and the formula will work correctly.

 

best response confirmed by Bridget_T (Copper Contributor)
Solution

@Bridget_T 

Another variant

=LOOKUP(2,1/ISNUMBER(SEARCH($R$1:$R$4,B2)),$S$1:$S$4)

Thanks@Sergei Baklan this worked just as I wanted - Much appreciated.

This was the formula I was working towards - I am still getting #value on the search. However @sergi Baklan suggestion worked - but thanks

@Bridget_T 

No problem! :)

1 best response

Accepted Solutions
best response confirmed by Bridget_T (Copper Contributor)
Solution

@Bridget_T 

Another variant

=LOOKUP(2,1/ISNUMBER(SEARCH($R$1:$R$4,B2)),$S$1:$S$4)

View solution in original post