Excel: Look up for the text contains

1
votes

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.

data provided by user

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

1
exceltextexcel-formulacontainslookup
Thanks Byron! I have shared the image link. Please let me know if that helps. – rkeeth
In your example are the values in B2:B7 the only values you need to look up against of are there other values? – Alex P
B2:B7 is just a sample. The actual values are much more and it will be updated in future by adding more. – rkeeth
Any thoughts @AlexP . – rkeeth
And what about the look up table? – Alex P

1 Answers

2
votes

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

ranges and results

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.