Problem: HLOOKUP function as an array formula not working!

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

Sure - here you go, with a couple of annotations...

Many thanks

Hi sixth sense,

Thanks but my initial question stated that the formula has to be a single ARRAY formula (i.e. using CTRL+SHIFT+ENTER) that works across all the cells, not just an HLOOKUP that insert into each cell.

I know plenty of ways to do it with individual non-array formulas in each cell - for example your one.

Do you know how to do it as an array? If you look at my example, you'll see that it isn't working for me - so i must be doing something wrong.

Many thanks,
Tom

Thanks all for looking:

FDibbins - sure, good question - three reasons mainly 1) My computer is not the best and given that my original data set is so huge, it seems to be coping a lot more easily with Array formulas that cover large amounts of cells rather than hundreds of thousands of individual formulas. 2) It's far neater for me and given that I want everything automated on my original data set, before I hand it over to someone else, I want there to be fewer ways in which the formulas could be messed up by a third party - arrays have the advantage scaring of most laymen away! 3) I'm now genuinely curious - is it actually possible to use HLOOKUP as an Array formula?

newdoverman - curious to hear about any more efficient ways of processing large amounts of data if an array formula isn't the best way here. The formula you've given unfortunately won't work for my purposes because it doesn't replicate the HLOOKUP functionality. In your example, all that would achieve is to copy the data, whereas I will be wanting to actually pull data out of the original data, skipping columns that I don't want. So it has to be something that works like an HLOOKUP

Hope that clarifies.
Cheers,
Tom

Cheers for that, will take a look through - very useful with my aging computer!

Don't suppose you've any other thoughts on the HLOOKUP as an Array? I'm starting to think that HLOOKUP just doesn't work when you use it as an array? It simply doesn't seem to be picking out the anything past the first line of data...

BTW - I can make the HLOOKUP array formula work across any single row or down any single column, I just can't use the same HLOOKUP array formula across multiple rows and columns - very frustrating and makes me think that I'm missing something simple...

Thanks everyone for all the tries and effort put in, very much appreciated. Apologies for the delay as have been away.

TMS, to answer your question - no, it isn't solved unfortunately. My growing suspicion is that HLOOKUPs simply do not actually work as arrays - a suspicion that is supported by the fact that no one has attempted to tackle the problem directly from that point of view (i.e. trying to use HLOOKUPs)

Your response using the INDEX function, as you mentioned, regrettably doesn't tackle/solve the issue of being able to change the columns around/ skip columns etc that you could do if you used an HLOOKUP. Aside from that, a simpler way of doing the same as what you've done is to simply use the array {=A3:D5}, which would recreate the cells using a single array formula but wouldn't require the use of the INDEX function (which would slow processing on a large data set).

So I'm still very interested if anyone can come up with a novel way of tackling/ solving my original post, but I'm not holding my breath as I think it may not be possible!