VLOOKUP Query

[VegasNath]

Hi,

This formula is in I4035
=VLOOKUP((B4035&"WRO"),A:A,1,FALSE)
But I only need to lookup A4:A4035
But when dragged down to I4036, becomes:
=VLOOKUP((B4036&"WRO"),A4:A4036,1,FALSE)

IOW, lookup row 4 to the row containing the formula, but not below. Is this possible?

[PJ_in_FL]

Try making the lookup ranges absolute instead of relative.

[VegasNath]

Yes, I need the upper range absolute and the lower range relative.

Thanks PJ.

[PJ_in_FL]

To make a relative lower limit of the range, in I4036 use the formula:
=VLOOKUP((B4036&"WRO"),$A$4:offset(B4036,-1,-1),1,FALSE)

The offset function should set the lower limit to one row less and one column less than the referenced location, i.e. to row 4036-1 = row 4035 and column (b)-1 = column (a). Does this accomplish what you want?