VLOOKUP based on a substring in cell to be searched in column

[Ekser]

Hello,

I'm depserately trying to do the following: I have a list of data which I'd like to be used in a sort of VLOOKUP function. The list looks as follow

Data sheet
Data.jpg

Then I have a working list containing a cell with some string values, for instance:

working.jpg
So here in my D1 cell I need to return a value based on a substring in cell E1, i.e. Phone House. Based on this value, I'd like Excel to find the corresponding row in table in Data sheet and return its corresponding value from column Vend for instance, like in a typical VLOOKUP function. The challenge here is that I cannot find a way to search within the Working sheet for a value in a string corresponding to one of the entries under Text in my Data sheet.

Any ideas?

Cheers,
Ekser

[JBeaucaire]

Try this:

=LOOKUP(2, 1/(ISNUMBER(SEARCH("*" & Data!$D$2:$D$4 & "*", Working!E1))), Data!$A$2:$A$4)

[JBeaucaire]

How'd it go?

[Ekser]

Hello and thanks for your answer!

Unfortunately it didn't work - it always returns 0. I also don't really get the ISNUMBER check - it is supposed to be a string/text.

Any idea..?

[JBeaucaire]

Well, the formula works based on the information provided. If you post workbooks instead of pictures of workbooks, we can show the working formula from the outset. (and it's a forum rule, actually. )

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook. I'll show you there.

[Ekser]

Indeed it will be much easier with a file, so I've uploaded file PR.
Sheet Data contains data to be used: column Text has the value that should be found back in Sheet Working - cell Description. If string found (in this case ELECTRABEL, ATHLON) I'd need to retrieve its corresponding fields from Data sheet i.e. Vend, Cat and Subcat and feed them back in Working sheet under Vendor (where I've tried your formula) Category and subcat.

[JBeaucaire]

You expanded the formula to include blank cells, that's the problem. Only use the range of cells with values to keep the formula small:

=LOOKUP(2, 1/(ISNUMBER(SEARCH("*"&Data!$D$2:$D$3&"*", $C2))), Data!$A$2:$A$3)

... or we have to add one more test to exclude blank cells:

=LOOKUP(2, 1/(ISNUMBER(SEARCH("*"&Data!$D$2:$D$27&"*", $C2))*(Data!$D$2:$D$27<>"")), Data!$A$2:$A$27)


If that takes care of your need, please click Thread Tools above your first post and mark this thread as SOLVED.

[Ekser]

Belissimo!
The second formula works like a charm, thanks!