Issue with VLOOKUP

[armsiee]

Excel 2003:

Apologies if I am doing something incredibly stupid but its been a
very long month and way behind on a project and this is driving me
crazy!

references in sheet 1

B
2 84856
3 84857
4 377738
5 406787
6 406788

Same references in sheet 2 in a range named DC_Issues

B C
2 84856 1
3 84857 2
4 377738 3
5 406787 4
6 406788 5

Then in column J on Sheet 1 formula VLOOKUP(Bx,DC_Issues,2,TRUE)

Returns
J
2 1
3 2
4 2
5 2
6 2

Have confirmed that the formula has been copied down correctly. Range
defined. Yet whatever i do (and I have tried numerous things!) I
cannot get the right value to be returned. I have been using this
function for years and haven't come accross this before.

Any help greatly appreciated.

 

[Pete_UK]

Your data on Sheet2 needs to be sorted to use VLOOKUP with the fourth
parameter set to TRUE.

Alternatively, set it to FALSE or 0, like this:

=VLOOKUP(B1,DC_Issues,2,0)

then copy down - this looks for an exact match.

Hope this helps.

Pete

 

[John]

HI
I don't see your layout but your formula VLOOKUP(Bx,DC_Issues,2,TRUE)
If range_lookup is TRUE, the values in the first column of table_array must
be placed in ascending order: 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise
VLOOKUP may not give the correct value. If range_lookup is FALSE,
table_array does not need to be sorted, and will give you the exact answer.
HTH
John

 

[xlmate]

Your formula basically is correct,
however, if you are looking for an exact
match, use FALSE or 0 for the 4th parameter
in the formula, such as:
=VLOOKUP(Bx,DC_Issues,2,FALSE)

HTH

--
Your feedback is very much appreciate, pls click on the Yes button below if
this posting is helpful.

Thank You

cheers, francis