Avoiding Wildcard effect in VLOOKUP

[Markshnier]

I have data which ends in an *. I found out the hard way that a VLOOKUP will treat the * as a Wild Card. Is there a way to have Excel not treat that trailing * as a Wildcard?

For example cell a1 contains a part number text value of A12345*
Cell b1 contains =VLOOKUP(A1, my range goes here, 2, FALSE)

My problem is that I want an exact match on part number 12345*

Hoping someone can help me.

Mark Shnier

[Markshnier]

I think I figured it out. Ii have to manipulate my data to detect it ending in an * and replace it with ~*

for example my 12345* needs to become 12345~*

http://office.microsoft.com/en-us/ex...093351033.aspx


Mark Shnier

[rylo]

Hi

You could also do something like

=VLOOKUP(SUBSTITUTE(A1,"*","~*"),A2:B3,2,FALSE)


rylo