Excel 2007 : hlookup copy and paste across an array help

Good afternoon. I have a simple hlookup formula that reads data from another sheet when the dates in row A aligns.
[=HLOOKUP($A$1,Sheet3!$A$2:$L$12,2,0)]

I can only copy and drag formula across each row at a time. To copy down the columns I have to amend the formula for each row.

I am looking for a way to copy and paste similar formula across a whole array in one go. Can anyone offer an alternative formula please.

Thanks


Martin

Hi Martin,

Try using the formula with Row function:-

=HLOOKUP($A$1,Sheet3!$A$2:$L$12,ROW(A2),0)

Regards,
DILIPandey

<click on below 'star' if this helps>

That fixes copy against $A$1 which is ok pasting down the column. This works if I change $A$1 to $B$1, then $C$1 etc and the copy and pasting down each individual column


I need to copy and paste across Columns A, B, C etc to E.
Do you have a quick way of pasting down across columns A to E?

Thanks


Martin

Hi Martin,

Try removing $ sign from left of the letter.. $A$1 would be A$1 and then drag to right....
For quick way of pasting down.. press Ctrl + D
For quick way of pasting ABCDE.. press Ctrl + R

Upload the sample file in case of any issues. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

Thankyou. I am getting better at Excel and grateful for help like this. Solved. You're a star my friend

Very Best Regards

Martin

you are welcome .. .

Cheers

Regards,
DILIPandey

<click on below star if this helps>