if Contains Vlookup Formula

Hi,

I am not sure if this will be possible or not, but...

I am trying to do a Vlookup against a description (as the lookup value), but instead of returning the corresponding value for an exact match...I want it to return the corresponding column if part of the lookup key is contained within the description (lookup value).

I have attached a spreadsheet that may help give clearer context to my question.

Any help would be extremely appreciated.


Sam.

This works, thankyou so much for your help and extremely quick response.

Originally Posted by DGagnon

i think this will accomplish what you are looking for

=VLOOKUP(LEFT(A2,5),D:E,2,0)

This also works, thankyou so much for your help and extremely quick response.

Originally Posted by Haseeb A

Hello Sam, try this in B2

=IFERROR(LOOKUP(9E300,SEARCH(D$2:D$10,A2),E$2:E$10),"")

Then copy down

This ALSO works! Thanks for your help.

Yeah, the repeated values in the reference list poses a problem.

This has happened because the reference list was originaly created using a left trim formula on a free text description.

Thanks again.

Originally Posted by JieJenn

To me it seems possible if I use wild card. Because you have two agric in short key, the lookup is going to extract the first value it found.

hey there

i have a similar query, somewhat easier i think.

i'm trying to combine VLOOKUP and IF fuctions for cells that contain certain 4-digit numbers. The idea is that if the cells in column E contain any of the values in column A then it returns the classification value in cell D

any help would be great!