Ihave this formula:

=HLOOKUP('Hlookup table'!C2,'Hlookup table'!D2:O3,2)

as I scroll I would like the lookup value and the range to increment by 2,
so the next row would be:

=HLOOKUP('Hlookup table'!C4,'Hlookup table'!D4:O5,2),

the following offset formula returns the correct lookup value:

=OFFSET('Hlookup table'!C$2,(ROW()*2)-6,0)

Can i use the offset to move the range? I was try to solve a lookup problem
where i had problens sorting the data into ascending order and returning them
to their orginal position for other calculations.

Thanks

Stephen Christian

Hi,
if you want to drag right use this

=HLOOKUP(INDIRECT("'Hlookup table'!C" &
COLUMN(B2)+(COLUMN(A2)-1)),INDIRECT("'Hlookup table'!D" &
COLUMN(B2)+(COLUMN(A2)-1) & ":O" & COLUMN(B2)+(COLUMN(A2))),2)

or to drag down

=HLOOKUP(INDIRECT("'Hlookup table'!C" &
ROW(B2)+(ROW(A2))-2),INDIRECT("'Hlookup table'!D" & ROW(B2)+(ROW(A2))-2 &
":O" &ROW(B2)+(ROW(A2))-1),2)

Mike




"Stephen Christian" wrote:

Ihave this formula:

=HLOOKUP('Hlookup table'!C2,'Hlookup table'!D2:O3,2)

as I scroll I would like the lookup value and the range to increment by 2,
so the next row would be:

=HLOOKUP('Hlookup table'!C4,'Hlookup table'!D4:O5,2),

the following offset formula returns the correct lookup value:

=OFFSET('Hlookup table'!C$2,(ROW()*2)-6,0)

Can i use the offset to move the range? I was try to solve a lookup problem
where i had problens sorting the data into ascending order and returning them
to their orginal position for other calculations.

Thanks

Stephen Christian

So it looks like you want the first formula in B3 copied down, if so
try

=HLOOKUP(OFFSET('Hlookup table'!C$2,(ROWS(B$3:B3)-1)*2,0),OFFSET
('Hlookup table'!D$2:O$3,(ROWS(B$3:B3)-1)*2,0),2)