I don't know what changed but I could replicate the issue now.

* is being taken as a wild card... I replaced all *s with $s and did NOT get
the match as expected...

Can you remove all * in your lookup table? I don't think so but if you can
then you can use someting like;

=VLOOKUP(SUBSTITUTE(A3,"*",""),A1:A10,1,FALSE)

I will post again if I find a solution.

"mark" wrote:

The values are literal strings... item numbers. They should not be referring
to any other cells.

I've simplified the example of my problem, and it's giving the same result.

In a new workbook, with only one sheet, I've entered these contents:

A1: List
A2: a
A3: a*BB*c
A4: c

B1: a*b*c
C1: =VLOOKUP(B1,tblList,1,FALSE)

Cells A1 to A4 are given the range name, tblList

the value in B1 is not in the list, yet C1 is returning a match, the value
in cell A3.

I need it NOT to do that.

Thanks for looking at it.


"Sheeloo" wrote:

I put 61*080*C51*022 in A1
and 61*80*C51*022 in C1

Entered the following formula in B1
=VLOOKUP(A1,C:C,1,FALSE)

The result (as expected) I got was #N/A

Does C51 refer to a cell containing numbers... Is the value in A1 or C1
being treated as a formula?

If I put a = sign in front of A1 and C1 and put a number in C51 then I have
identical values in both A1 & C1


"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.