hlookup- move range by 2 rows with fill.
 

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

hlookup- move range by 2 rows with fill.
 

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

hlookup- move range by 2 rows with fill.
 

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)