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.

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

On Jan 30, 4:18*pm, armsiee wrote:
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.

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


"armsiee" wrote in message
...
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.

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



"armsiee" wrote:

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.