Partial Vlookup if a text is found

[bart90]

Hi Everyone,
I've a problem with figuring out how to use Vlookup to find only a part of a text.
So as an example, in the first column I have a list of fruits which are randomly inserted between some characters.
I'd need a formula which would look for a fruit in the first column and if a fruit is found then return its colour.
Vlookup with approximate match doesn't help here.

1111*Apple*1111		Apple	Green
-Apple-		Apple	Green
---Pineapple---		Pienapple	Yellow
*Pineapple*		Pineapple	Yellow

Thanks a lot for your help!

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

[oeldere]

I used 2 helpcolumns to get the result.

C3 (green cells) are the result.

D3 =IFERROR(SEARCH($D$2,$A3,1),0) and drag down

E3 =IFERROR(SEARCH($E$2,$A3,1),0) and drag down

C3 =IF(AND(D3=0,E3=0),"error",IF(D3>0,$D$1,$E$1)) and drag down

See the attached file.

[bart90]

HI Oeldere,
Thanks for your suggestion, it would indeed work if I had only two 'fruits' and 'colors' in my report.
In your formula the number of choices is restricted to 2 - IF(D3>0,$D$1,$E$1).
The problem is that I have around 16K lines in which 'fruits' appear and the 'fruits' amount to around 230 (along with 230 'colors')
Using nested formula for if might be an option but perhaps there's an easier way?
Thanks

[Bo_Ry]

Please try at C3

=LOOKUP(0;-FIND($D$3:$D$4;A3);$E$3:$E$4)

[oeldere]

The problem is that I have around 16K lines in which 'fruits' appear and the 'fruits' amount to around 230 (along with 230 'colors')

You did not add that is your question.


How come the data is so messy, maybe that could be a part of the solution.