Forum Discussion
Solved
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 th...
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.
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
