Excel: Look up for the text contains

Question

I need to do a lookup and return the value based on if the text contains it.

Please see my excel sheet below, What I need is a formula, which will help my 'Category' column to be filled 'Category' column of the look up table.

Note: Kindly see my excel sheet in below link.

I tried the formula '=VLOOKUP(B2,A13:B16,2,TRUE)' but it is not giving the expected result.

Answer 1

This is a slight twist on the normal "find this string inside a list of others". It requires an array formula that searches for matches using FIND.

Picture of ranges

Formula in cell A2 is an array formula (entered with CTRL + SHIFT + ENTER) and is copied down for each item. It searches in the list of lookup for an item that is included in item and returns the result from category associated with lookup.

=INDEX($E$2:$E$4,MIN(IF(IFERROR(FIND($D$2:$D$4,B2)>0,FALSE),ROW($D$2:$D$4)))-ROW($E$1))

How it works

• INDEX is returning from category and needs a row number to return
• The row number is determined by using FIND which will check if a string is included in part of another string. In this case, the string to search for is the lookup table and we are matching within the item.
• FIND will return #VALUE! if no match is found, this is converted to FALSE with IFERROR because #VALUE! will no work with MIN later.
• The IF will then return the ROW number or FALSE for the match that was found.
• MIN is used to convert the list of ROW numbers to the smallest number. This means that multiple matches are not handled.
• This ROW number is then used as the return for the INDEX. There is an offset applied here -ROW(E1) which allows for the data tables to start in a row other 1:1.