VLOOKUP

[Patrick]

I have a VLOOKUP table referenced from one sheet to another in the same workbook

EXCEL is acting strangely in that it refuses to return all the values from the LOOKUP table.

I have checked the range and am satisfied that it's right

VLOOKUP seems to stop looking without reason, or returns the wrong value, having done everthing OK up to that point

Is there a limit to the number if rows in which VLOOKUP can operate

The value is visible in the table from which I want it returned and is in the right range

I don't get either an N/A, ERR or VALUE returned;

the values are in the right sequence (already sorted);

values on similar sheets in the same workbook are correctly returned;

what am I doing wrong?

Please. please, anyone?

 

[JMay]

Post the formula you are using,...(for results)

I have a VLOOKUP table referenced from one sheet to another in the same workbook.

EXCEL is acting strangely in that it refuses to return all the values from the LOOKUP table.

I have checked the range and am satisfied that it's right.

VLOOKUP seems to stop looking without reason, or returns the wrong value,

having done everthing OK up to that point.

 

[Patrick]

=HLOOKUP(A20,'D+W L1'!$BP$118:$FW$121,4

This formula runs from A20 to A130 in the home sheet.

I am using the same format on another home sheet (referenced to 'D+W L0'!) and it works all the way from A20 to A130

The formula above only seems to read as far as EH 121 (A103), which is the last result it returns, but does not return FB121 (A110), which is the last value in the range

A VLOOKUP table

VLOOKUP(A26,'RMS L1'!$B$20:$C$130,2

This runs from A26 to A38 in the home sheet

It returns the right values up to C105 (A38) and thereafter returns incorrect values

 

[Hans]

You might check the tables the lookup formulas refer to.
Maybe, for example, the range that returns the incorrect
data has more or less digits than the range that returns
the data correctly, because it was copied in a different
way.

Or you might add FALSE to your formula (=HLOOKUP(A20,'D+W
L1'!$BP$118:$FW$121,4, FALSE); it will then only return
values that exactly match the lookup value.

regards,
Hans

-----Original Message-----
=HLOOKUP(A20,'D+W L1'!$BP$118:$FW$121,4)

This formula runs from A20 to A130 in the home sheet..

I am using the same format on another home sheet

(referenced to 'D+W L0'!) and it works all the way from
A20 to A130.

The formula above only seems to read as far as EH 121

(A103), which is the last result it returns, but does not
return FB121 (A110), which is the last value in the range.

A VLOOKUP table:

VLOOKUP(A26,'RMS L1'!$B$20:$C$130,2)

This runs from A26 to A38 in the home sheet

It returns the right values up to C105 (A38) and

thereafter returns incorrect values.