Use VLOOKUP using wildcard in the lookup table

Hi All:

I have a master list of info based on a 7 digit number.

Occasionally, I am given an alphanumeric that always starts with 0dp, instead of the 7 digit number.

Can I have a row that has the lookup value as "0dp*", so that anytime I get an alphanumeric starting with 0dp, such as "0dp:2gyd", it will return the given values for 0dp*, since they will always be the same?

I have attached an example. where sheet 1 is the master list and sheet 2 has the list I want to attach the values to. I want VLOOKUP (or some alternative) to recognize odp* as the acceptable lookup spot for each of the 0dp's in the sheet 2 list and return the "0", instead of #N/A.

Thank you

EliotK

Hi Ali:

Actually, I am using the ancient 2007 version. It works great for me.

Eliot

sheet2 worksheet name , B1 cell formula ,drag down
=LOOKUP(,-FIND(SUBSTITUTE(Sheet1!$A$1:$A$5,"*",),A1),Sheet1!$B$1:$B$5)

Thank you for your response.

That did seem to work for the sample workbook, but not for my expanded one.

I actually figured out what to do:

I added IF(A1>9999999,0,VLOOKUP etc.)
That worked for me.

Eliot

PS I could not find the prefix SOLVED, so I typed it in