SOLVED
Home

Refining an If statement

%3CLINGO-SUB%20id%3D%22lingo-sub-872282%22%20slang%3D%22en-US%22%3ERefining%20an%20If%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-872282%22%20slang%3D%22en-US%22%3E%3CP%3ETable%201.col%20A%20has%20a%20list%20of%20comments.%20Col%20B%20I%20currently%20have%20an%20if%20statement%20to%20pick%20out%20recurring%20words%20to%20classify%20the%20comment.%20I%20want%20to%20refine%20this%20to%20make%20it%20more%20manageable.%20Any%20suggestions.%3CBR%20%2F%3E%3DIF(ISNUMBER(SEARCH(%22Luton%22%2CB2))%2C%22Van%22%2CIF(ISNUMBER(SEARCH(%22Mini%22%2CB2))%2C%22Car%22%2CIF(ISNUMBER(SEARCH(%22Canoe%22%2CB2))%2C%22Boat%22%2CIF(ISNUMBER(SEARCH(%22Mitsubishi%22%2CB2))%2C%22Car%22))))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-872282%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-872330%22%20slang%3D%22en-US%22%3ERe%3A%20Refining%20an%20If%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-872330%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F396549%22%20target%3D%22_blank%22%3E%40Bridget_T%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20create%20a%20Lookup%20Table%20like%20this...%20(in%20this%20case%20the%20Lookup%20Table%20is%20created%20in%20the%20range%20R1%3AS4)%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20428px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F133310i29177CFB05C24A6D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Lookup%20Table.jpg%22%20title%3D%22Lookup%20Table.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20you%20can%20simply%20use%20this%20formula...%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DINDEX(%24S%241%3A%24S%244%2CMATCH(TRUE%2CISNUMBER(SEARCH(%24R%241%3A%24R%244%2CB2))%2C0))%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20change%20the%20ranges%20referred%20in%20the%20above%20formula%20based%20on%20the%20range%20of%20your%20Lookup%20Table%20and%20the%20formula%20will%20work%20correctly.%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-872342%22%20slang%3D%22en-US%22%3ERe%3A%20Refining%20an%20If%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-872342%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F396549%22%20target%3D%22_blank%22%3E%40Bridget_T%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnother%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DLOOKUP(2%2C1%2FISNUMBER(SEARCH(%24R%241%3A%24R%244%2CB2))%2C%24S%241%3A%24S%244)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-873879%22%20slang%3D%22en-US%22%3ERe%3A%20Refining%20an%20If%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-873879%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%20this%20worked%20just%20as%20I%20wanted%20-%20Much%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-873881%22%20slang%3D%22en-US%22%3ERe%3A%20Refining%20an%20If%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-873881%22%20slang%3D%22en-US%22%3EThis%20was%20the%20formula%20I%20was%20working%20towards%20-%20I%20am%20still%20getting%20%23value%20on%20the%20search.%20However%20%40sergi%20Baklan%20suggestion%20worked%20-%20but%20thanks%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-873986%22%20slang%3D%22en-US%22%3ERe%3A%20Refining%20an%20If%20statement%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-873986%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F396549%22%20target%3D%22_blank%22%3E%40Bridget_T%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENo%20problem!%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Bridget_T
Occasional 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.

 

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!

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