Excel lookup every word in a cell and match with a list

Question

I am trying to come up with a excel formula to Look Up every word separated by either a space or comma in a cell, match each of the words against a list of words and return the found word in another column. As an example:

So the ColorFamily column should be a formula I have tried using VLOOKUP e.g.

 =VLOOKUP(H3,color_family!$A$3:$A$19,1,FALSE)

But the limitation is that it does not iterate through every single word in the cell. Is it possible to do this using Excel Formula or is VBA required?

You currently work on the whole cell. If you want to do this on a word by word basis you will have to split your string into its individual words and look-up those. Depending on how long your strings are, you could do with multiple formula's, but VBA would suffice as well. — Luuklag, Jul 09 '18 at 07:52
Have a look at [TEXTJOIN for xl2010/xl2013 with criteria](https://stackoverflow.com/questions/50716550/textjoin-for-xl2010-xl2013-with-criteria/50719050#50719050). I'm pretty sure you can get that working. — , Jul 09 '18 at 07:53
@Luuklag: can you split the content of a cell over different cells, not using VBA? (I thought this was only possible using VBA, not formulas) — Dominique, Jul 09 '18 at 08:24
@Dominique, making use of `Find()` and `Left()` or `Right()` you could seperate it into different cells. — Luuklag, Jul 09 '18 at 09:39
@Luuklag: I understand that, using those formulas, one can be able to get the first word in one cell (by putting the corresponding formula in that one cell), the second in another (by putting the corresponding formula in that other cell), the third in yet another (…), …, but if you don't know in advance the maximum amount of words you might encounter in a cell, I believe you won't be able to get the job done without using VBA. — Dominique, Jul 09 '18 at 09:58
@Dominique, I think you could use `Offset()` to do the trick when you count the amount of spaces in a string. This can easily be done with `Len()` and `Replace()`. Also don't forget the power of `R1C1` referencing. But you will end up using a lot of helper columns. So I agree VBA might be the better solution here, unless you know that the amount of words will be limited. — Luuklag, Jul 09 '18 at 10:04

score 3 · Accepted Answer · answered Jul 09 '18 at 12:15

3

Enter as an array formula (ctrl+shift+enter):

=TEXTJOIN(" ",TRUE,IF(ISERR(FIND(color_family!$A$3:$A$19,H3)),"",color_family!$A$3:$A$19))

answered Jul 09 '18 at 12:15

jblood94

10,340
1
10
15

score 1 · Answer 2 · answered Dec 11 '20 at 19:03

I couldn't make much sense of the accepted answer but here is a similar approach. This works in Excel 365, and depends on its dynamic array functionality to work.

Here is the spreadsheet layout I am working with:

I have used spaces to separate the values in the colour list but the solution could be generalised to handle commas etc.

The steps I've used to build the formula needed are:

Group valid list into a single string using TEXTJOIN: TEXTJOIN(",",TRUE,$A$7:$A$9)
Split the Colour cells into columns of words (uses dynamic array functionality). There is a write up on how to do this here: https://www.mrexcel.com/board/excel-articles/split-text-cell-into-columns-of-words.19/ e.g. for A2 this formula produces Black and Red in separate columns

TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),SEQUENCE(1,LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1,1,LEN(A2)),LEN(A2)))
Use FIND to look for the text in each column above, in the valid list
If FIND returns a number (checking with ISNUMBER) return the text, otherwise ""
This is all still in separate columns so now use TEXTJOIN to combine the results together in a comma separated list.

Final formula in B2:

`=TEXTJOIN(",",TRUE,IF(ISNUMBER(FIND(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),SEQUENCE(1,LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1,1,LEN(A2)),LEN(A2))),TEXTJOIN(",",TRUE,$A$7:$A$9))),TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),SEQUENCE(1,LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1,1,LEN(A2)),LEN(A2))),""))`

which can be copied into B3, B4 etc giving final result:

Excel lookup every word in a cell and match with a list

2 Answers2