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

1
votes

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:

Color

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?

2
excelexcel-formula
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
Have a look at TEXTJOIN for xl2010/xl2013 with criteria. I'm pretty sure you can get that working. – user4039065
Have you tried find() – Solar Mike
@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
@Dominique, making use of Find() and Left() or Right() you could seperate it into different cells. – Luuklag

2 Answers

3
votes

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))

1
votes

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:

enter image description here

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:

  1. Group valid list into a single string using TEXTJOIN: TEXTJOIN(",",TRUE,$A$7:$A$9)

  2. 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)))

  3. Use FIND to look for the text in each column above, in the valid list

  4. If FIND returns a number (checking with ISNUMBER) return the text, otherwise ""

  5. 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:

enter image description here