Strange VLookup problem

[Gordon]

I have a list of part numbers with descriptions in a data range.
On a separate worksheet, the user types in the part number and a vlookup
puts in the part description in another column.
The problem seems to occur with two part numbers only, 2228-120 and
2228-021.
If 2228-120 is typed into the part number cell, the vlookup returns the
description for part number 2228-021!
How do I correct this? (Everything else works properly AFAIK!)

Thanks

 

[Gordon]

I have a list of part numbers with descriptions in a data range.
On a separate worksheet, the user types in the part number and a vlookup
puts in the part description in another column.
The problem seems to occur with two part numbers only, 2228-120 and
2228-021.
If 2228-120 is typed into the part number cell, the vlookup returns the
description for part number 2228-021!
How do I correct this? (Everything else works properly AFAIK!)

Thanks

I've actually fixed it by moving the 2228-120 line in the data table to it's
numerical position - but I thought that VLOOKUP looks up the whole of the
table, and doesn't depend on the table being in numerical or alphabetical
order?

 

[JulieD]

Hi Gordon

VLOOKUP actually has four parameters, and to me it sounds like you're only
using the first three (the mandatory ones) the fourth makes VLOOKUP do an
exact match (when False or 0 is used) if the fourth parameter is omitted
then true (approximate match) is assumed - and for this to work somewhat
properly the data must be sorted ascending.

My recommendation would be that you add the fourth parameter to your VLOOKUP
funciton.
=VLOOKUP(thing_to_lookup,table_array,col_num,FALSE)

Cheers
JulieD

 

[Gordon]

Hi Gordon

VLOOKUP actually has four parameters, and to me it sounds like you're only
using the first three (the mandatory ones) the fourth makes VLOOKUP do an
exact match (when False or 0 is used) if the fourth parameter is omitted
then true (approximate match) is assumed - and for this to work somewhat
properly the data must be sorted ascending.

My recommendation would be that you add the fourth parameter to your VLOOKUP
funciton.
=VLOOKUP(thing_to_lookup,table_array,col_num,FALSE)

Cheers
JulieD

many thanks for the info!

 

[JulieD]

you're welcome

many thanks for the info!