Hlookup+offset

[rdev]

My problem is i have merged cells A1,B1,C1 and input a text value "DR" in Cell A1, say in Sheet1
I want to pull figures in from row A8,B8 and C8 from Sheet1 and put it in another worksheet say Sheet2
say in Rows 10 i.e A10,B10,C10.
I have input "DR" in cells A1 in sheet2.
This formula works when in cells A2 in sheet2=HLOOKUP($A$1,Sheet1!A1:I13,8,0)
however, and as expected ,when i drag the formula to the right it does not work it gives me the result of the Hlookup for column A, how can i sort this out , am thinking of offset but have no clue how to adapt the formula to pull out the values from B8, C8 in sheet 2 , in B2 and C2 . I need this soln to pull out figures from a massive spreadsheet having merged header rows

 

[Andrew Poulsom]

One way:

=INDEX(Sheet1!$A8:$I8,MATCH($A1,Sheet1!$A1:$I1,FALSE)+COLUMN(A1)-COLUMN($A1))