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.