Hi there,

I'm simplifying my problem here down to the bare minimum so as not to confuse I hope!

(My actual data is over several excel sheets with thousands of row and columns of data)

If I set my data up like this:

A B C D E F G H
1 John Mark Luke Dan John Mark Luke Dan
2 10 20 30 40
3 50 60 70 80

My goal is to replicate Cells A2:D3 into E2:H3 using HLOOKUP as an array formula.

Obviously I can do this if I enter the following non-array formula into each of cells E2:H3
=HLOOKUP($E1,$A$1:$D$3,ROW(),FALSE)

My question is how do I replicate this result using a single HLOOKUP array formula across cells E2:H3?

Currently I'm doing this single array formula across cells F2:I3:
={HLOOKUP(E1:H1,A1:D3,ROW(),FALSE)}

And i get the following result on the right and table:

A B C D E F G H
1 John Mark Luke Dan John Mark Luke Dan
2 10 20 30 40 10 20 30 40
3 50 60 70 80 10 20 30 40

As you can see, the second line (cells E3:H3) are not calculating as I'm hoping.

I'm sure I'm doing something obvious but just can't see it.

Any help would be greatly appreciated!

Cheers,
Tom