• 464K Members
• 11.2K Online
• 560K Conversations
SOLVED

## Refining an If statement

Occasional Contributor

# Refining an If statement

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

# Re: Refining an If statement

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

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.

Solution

# Re: Refining an If statement

Another variant

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

# Re: Refining an If statement

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

# Re: Refining an If statement

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

# Re: Refining an If statement

No problem!

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies