Text and Numbers in VLOOKUP

[Francis]

I've set up a VLOOKUP formula to search for entries containing text and numbers (eg. ABC12345) within an IF statement. The data to be searched occasionally contains a suffix which needed to be removed so I created a column of LEFT formula to reduce the data down to the required number of digits. Using this column as the search area, or the original column, the VLOOKUP only finds the first entry containing the initial letters of the data (eg. ABC) and ignores the numbers, therefore not producing the correct answer where there is more than one entry starting with the same letters.

I've tried formatting the cells as numbers, text and all, but nothing works.

The full formula is =IF(VLOOKUP(K5;P5:P300;1)=0;I2;H2)

K5 = the ABC12345 reference number to search for
P5:P300 = the range produced by the use of the LEFT formula
I2 = "not found"
H2 = "found"

I get the feeling I'm doing something really silly, but I just can't spot it. The only unusual element about this spreadsheet is that it has been imported from an outside source as a csv.

Any help would be most appreciated.

Francis

[noranthon]

Add the fourth parameter to VLOOKUP. Presumably, you want an exact match, so use 0 (=FALSE). A new Help section was to be included, IIRC, in 2.1. I was not favourably impressed with the new section but it was an improvement on the old one.

My best effort at rationalising the apparent rationale for the fourth parameter is that TRUE(1) means you are willing to accept the first near match. You can only use TRUE if the lookup column is sorted in ascending order. You could look at the issue I filed, not knowing rewriting had already occurred.
_________________
search forum by month

[Francis]

Thanks, that has worked perfectly.

Francis