vlookup retunrning a match, when not a match...
 

Excel can use wildcards:
* for any characters
? for any single character
~ as an "escape" character to indicate that the next * or ? should be treated as
an asterisk or question mark--not as wild cards.

So if you're using an =vlookup() for instance, you can avoid wildcard problems
with a formula like:

=VLOOKUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"~"," ~~"),"?","~?"),"*","~*"),
Sheet2!$A:$B,2,FALSE)



mark wrote:

I have two item numbers:

61*80*C51*022 in a list of item numbers, tblList

61*080*C51*022 in a single cell, call it a1

the function =vlookup(a1,tblList,1,0) is returning a match on that.

they are not identical... one is *80* , the other is *080* .

I think it's because of a wildcard search that it's calling it a match?

I need it to be able to distinguish the difference. It's causing my report
to be wrong.

Help?

thanks.

--

Dave Peterson

vlookup retunrning a match, when not a match...
 

Thanks, that's one step expanded (for the ~) from what I came up with
earlier, and posted above.

I'll implement it tomorrow, probably.



"Dave Peterson" wrote:

Excel can use wildcards:
* for any characters
? for any single character
~ as an "escape" character to indicate that the next * or ? should be treated as
an asterisk or question mark--not as wild cards.

So if you're using an =vlookup() for instance, you can avoid wildcard problems
with a formula like:

=VLOOKUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"~"," ~~"),"?","~?"),"*","~*"),
Sheet2!$A:$B,2,FALSE)



mark wrote:

I have two item numbers:

61*80*C51*022 in a list of item numbers, tblList

61*080*C51*022 in a single cell, call it a1

the function =vlookup(a1,tblList,1,0) is returning a match on that.

they are not identical... one is *80* , the other is *080* .

I think it's because of a wildcard search that it's calling it a match?

I need it to be able to distinguish the difference. It's causing my report
to be wrong.

Help?

thanks.

--

Dave Peterson