=INDEX($B$3:$B$6,MIN(IF(--LEFT(IF(LEFT(A3:A6,LEN(A1))<"",A3:A6),LEN(A1))=A1
,ROW(A3:A6)-MIN(ROW(A3:A6))+1,"")))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Clark Shiao" wrote in message
...
Hi,

Thanks for the tip, but how can I incorporate the ~ into the A1 field? I
have many fields that contains the wildcard characters, I don't know how
to
get teh ~ into the cell other than change the cell value and not use the
A1
as the lookup reference.

10073661*** =VLOOKUP(A1,$A$3:$B$6,2,FALSE)

10073661010 5
10073661015 10
10073661083 15
10073661*** -17289


"Clark Shiao" wrote:

hi, I am trying to search for 10070162*** as a whole string text, the
asterisks do not represent wildcard characters. When I use 10070162***
as
the lookup value in vlookup, it brought back results for 10070162001,
10070162002 etc. Is there a way to disable Excel from treating the
asterisks
as wildcard?