Excel VLOOKUP and SEARCH combination

Question

I'm trying to search for part of a text string, in a column of text and return the second column. Hopefully this will make more sense with an example (note that this example is made up - I cannot post the exact data I'm using but this is similar to it).

For example:

A                            D               E

Really good dog             Good dog         text1
red dog collar              Brown dog        text2
Brown Toy dog               big dog          text3
                            dog collar       text4
                            dog walking      text5


 A                    B       

Really good dog       text1
red dog collar        text4
Brown Toy dog         Not Found

So column D is searched through to provide a match to the first value in column A, if there's a match it returns the corresponding text in column E, if there is no match it returns "Not Found". As you can see from the expected results, column B, the string "Good dog" appears within "Really good dog" so "Really good dog" has a result of "text1". Note that case does not matter.

I've been playing around with

=(VLOOKUP("*"&DCE!X2&"*",Lookups!$K$2:$L$19,2,FALSE))

which does not work because it's not all of the text string in the cell that could be matched to a value. I've tried using

=IF(SUMPRODUCT(--(NOT(ISERR(SEARCH($A$1:$A$3,C1)))))>0,"1","") 

which would be great if I could swap the range with the cell within SEARCH.

I know there's a MATCH version of these formulae but I haven't been able to get that to work either.

The only formula I have working is a huge nested IF statement

=IF(ISNUMBER(SEARCH(Lookups!$K$2,X2)),Lookups!$L$2,IF(ISNUMBER(SEARCH(Lookups!$K$3,X2)),Lookups!$L$3,IF(ISNUMBER(SEARCH(Lookups!$K$4,X2)),Lookups!$L$4,IF(ISNUMBER(SEAR......

which I do not want to be creating for 100+ items to search for. This sees if the first value in the search column (column D) is contained within the lookup string (column A) and if it is it returns the result (column E) and if it is not it returns "Not Found".

Also, the cell references in the above formulae do not directly relate to the example, they're just examples of the formulae I have been trying (I've found on various posts on this site).

Any advice would be greatly appreciated. Sorry if this is difficult to follow through, I'm fairly new to Stackoverflow.

Solution

Nice question! This works for the example you provided:

It is an array formula, so it needs to be confirmed with Control+Shift+Enter.

What it does is it creates an array of Find's and casts them to boolean (the NOT((NOT...)) part. Then the -- part converts the boolean to 1 and MATCH finds the place of that 1 in the array, which is the row that we need. This will work as long as the data start from row 1, but it can be easily modified to work otherwise. I am not entirely sure it meets all your needs though.. For example, a potential issue is that if there are two identical strings in column B, it will return the upper one, due to the way the MATCH() function works.

Sneaky but it probably does the job!

Comments

[LauraB]

This works great, thank you very much for your help! There won't be identical strings in column B so that isn't an issue. I can now play around with that formula so I can have a slightly different layout. Also, I changed FIND( to SEARCH( so it is case insensitive. Again, thanks for your help.