Wildcard in Excel

[Guest]

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?

 

[Bob Phillips]

Use

10070162~*~*~*

--

HTH

Bob Phillips

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

 

[Guest]

Use the tilde ~ character to override the wildcard. So, you'd search for
10070162~*~*~*

HTH,
Elkar

 

[Guest]

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

 

[Bob Phillips]

=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)