exclude vlookup returns for embeded codes

[acmiler]

I am running a vlookup and need to restrict the search data to an exact match and exclude all other returns which may have an exact match but the text match is embedded within a larger text string.

ie. looking for FRX in the following group:

RB-FRX40
TTFRX
FRX
GH-FRX53
IT-FRX00

vlookup is returning the first code (RB-FRX40) and I need FRX but I cannot seem to restrict the logic.

 

[Gerald Higgins]

Hi, welcome to the board.

Please can you post the full formula that you have ?

 

[acmiler]

Here ya go.

=VLOOKUP(A1235,'PRICE LIST WHOLEGOODS JULY 09'!A:D,3,FALSE)

 

[Gerald Higgins]

Hi. I don't think this formula is capable of matching FRX with RB-FRX40.

Are you saying you think that's what it's doing ?

 

[acmiler]

Yes, that is what it is doing. I have confirmed it. The value returned matches only the first line item. This is Excel 2003 if that helps any.

 

[Gerald Higgins]

OK. What value is in A1235 ?
What value is in 'Price List . . . .'!A that you think is being matched ?

 

[acmiler]

Hi,

The value in A1235 is: *FRX (no spaces) and the item it has matched to is: *RB-FRX40 with a returned value from column '3' of 350.00. I tested the returned value by changing the 350.00 value on the Pricelist to 1,549,853.00 (a value which does not exist anywhere else on the spreadsheet) and the returned value in the vlookup also changed to match it. There are 1530 lines in the data array and FRX occurs in various codes 11 times. The value being returned is on the first occurance of FRX which, as you can see, is embedded in a larger code. The one I want is the 3rd occurance and has no other text with it, just the *FRX by itself.

 

[Gerald Higgins]

I see.
So, it's NOT actually matching FRX with RB-FRX40.
It's matching *FRX with RB-FRX40.
These are two completely different things.
It's treating the * as a wildcard character, and looking for anything that contains FRX somewhere in the string.
But even then, I don't think it could match *FRX with RB-FRX40.
It could match *FRX* with RB-FRX40.
Is that what it's really doing?
Can you remove the * characters from cell A1235 ?