VLOOKUP with Special Value

**Andrew.Trevayne** · 10-21-2011, 03:46 AM

Hello,
Not sure if this is possible but how do you suggest I "VLOOKUP" a Cell (ColumnA) with value like "ABCD" and "EFGH" and "KLMN"
Asterisk can be different characters.
I'd like to pickup all that has this at the beginning.

VLOOKUP table is on Sheet2 Columns AB to pickup Value on B if theres a match.
Sheet 2 Column A has ABCD*, EFGH* and KLMN*, any thing from Sheet 1 that starts with this should be able to pickup the value on Sheet 2 Column B.
I hope I am making sense.
Appreciate your help. Thank you in advance.

Cheers,
Andrew

**TMS** · 10-21-2011, 05:05 AM

Can we see some real examples in a sample workbook.

Regards

**kvsrinivasamurthy** · 10-24-2011, 02:06 AM

Assuming Sheet2 has data from A2 to B7 as you have given,to get values in Sheet1 put this formula in Sheet1 B2 cell and drag down.

=SUMPRODUCT(--(LEFT(Sheet2!$A$2:$A$7,4)=Sheet1!A2),Sheet2!$B$2:$B$7)

**Andrew.Trevayne** · 10-25-2011, 04:37 AM

@kvsrinivasamurthy
Thank you for the formula but it was not picking the values as I was expecting.
Perhaps I didnt clearly explained what I needed.

@TMShucks
I have enclosed my dummy spread sheet

Here is what I was trying to achieve.
On Sheet 1 Column A I will have a set of records.
Sheet 2 Will be the lookup page (picking the value on column B)

Sheet 1 Column A values varries but typically begins with ABCD, EFGH, KLMN.
I need a formula to pickup value in sheet 2 based on the beginning values of Sheet1 Column A.
Something like ABCD* or EFGH* or KLMN*

Hope you guys understand what Im trying to explain. Thank you in advance.