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

[mark]

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.

 

[Niek Otten]

Always post your formula.

Perhaps you omitted the 4th argument to VLOOKUP? It should be FALSE

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

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

 

[mark]

I DID post my formula, and no, I didn't omit the 4th argment. FALSE is the
same to Excel as 0, which is what I used.

That is NOT the answer.

When I switch the fourth parameter to FALSE, not 0, it gives exactly the
same problem.

 

[Sheeloo]

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

 

[Niek Otten]

<I DID post my formula>

Yes, you did. Sorry, I overlooked it.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I DID post my formula, and no, I didn't omit the 4th argment. FALSE is the
| same to Excel as 0, which is what I used.
|
| That is NOT the answer.
|
| When I switch the fourth parameter to FALSE, not 0, it gives exactly the
| same problem.
|
| "Niek Otten" wrote:
|
| > Always post your formula.
| >
| > Perhaps you omitted the 4th argument to VLOOKUP? It should be FALSE
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| > |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.
| > |
| >
| >
| >

 

[mark]

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.

 

[mark]

The purpose of my quest is this... I have two lists of item numbers, most of
which are the same. But, I need to add any item numbers that are in the
second list, but not the first, to the first, to get a unique list from the
entire set.

I've thought of a way to handle this. The problem, as is, is that:

a*b*c

is finding the match:

a*BB*c

when I don't want that to be a match.

I think it's using a regular expression logic to find a pattern match...
read it as anthing b anything, and that matches anything BB anything.

But, the lengths are different. If I put in a check to see that the length
on the item found in the list, is the same as the length of the item searched
for, it will eliminate my problem.

but I still wish it was at least an option as to whether you want it to do
wild card searches or not.

I've further proved the problem by replacing the contents of cell A3 with
the word:

Northeast

and cell B1 with:

*east

The formula in cell C1, =VLOOKUP(B1,tblList,1,FALSE) returns a match:

Northeast

That could be very useful IF that's what I wanted it to do.

As is, it's mildly annoying.

 

[Sean Timmons]

I get the same issue. If you search for the *080* one, you won't find. But if
you search for *80* you DO get a match.

It appears VLOOKUP is counting the * as a wildcard search.

I tried entering an ' prior to the value and that di not resolve it. This is
a puzzler.

 

[mark]

It appears VLOOKUP is counting the * as a wildcard search.

Right, it is.

I did just find in the help where it says it's going to do that. It
explains that in the Remarks section, and says you can use a tilde ~ in front
of the * to sell it not to do that.

But, I can't change my original data before doing the lookup.

Working with another person here, we've come up with a formula to tell it to
do an exact match on both * and ?

=SUBSTITUTE(SUBSTITUTE(C1,"*","~*"),"?","~?")

where the value to be looked up is in C1.

This whole thing goes into a piece of VBA code, so the " is going to need to
be "" .

That should be 'pretty'.

But I can do it.

Thanks.

 

[Dave Peterson]

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)

 

[Sheeloo]

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]

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

I'll implement it tomorrow, probably.