Forum Discussion
excel check string for keywords, give out value of look up value
Hello,
I would need some help please:
Column A is a list strings of text in each cell
Column B is a list of keywords.
Column C is a list of categories.
I am looking for a formula that would allow to look up if the string in A1 contains any keyword found
in column B. If yes, give out the corresponding category found in column C. If the string does not contain any keywords, give back "no match".
I tried this:
=IFERROR(INDEX($C$2:$C$100;MATCH(1;COUNTIF(A1;"*"&$B$2:$B$100&"*");0));"No match")
But the formula gives back only 0.
Note: we use ; instead of , here.
Thank you for your help.
Dan
This is because the formula references ranges $C$2:$C$100 and $B$2:$B$100 but there are only keywords and categories in $C$2:$C$18 and $B$2:$B$18.
=IFERROR(INDEX($C$2:$C$100,MATCH(1,COUNTIF(A1,"*"&$B$2:$B$100&"*")*NOT(ISBLANK($B$2:$B$100)),0)),"No match")An alternative could be this formula if you want reference ranges $C$2:$C$100 and $B$2:$B$100. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
2 Replies
This is because the formula references ranges $C$2:$C$100 and $B$2:$B$100 but there are only keywords and categories in $C$2:$C$18 and $B$2:$B$18.
=IFERROR(INDEX($C$2:$C$100,MATCH(1,COUNTIF(A1,"*"&$B$2:$B$100&"*")*NOT(ISBLANK($B$2:$B$100)),0)),"No match")An alternative could be this formula if you want reference ranges $C$2:$C$100 and $B$2:$B$100. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.
- You might also want to try out XLookup
I’ve used this multiple time for similar projects
Here’s a YT how to reference link that might help you get started
https://youtu.be/4c0CLUER6nw?feature=shared
