Lookup multiple values and return multiple columns using a VLOOKUP array formula

[benkarst]

Hi all,

I have stumbled across something that I cannot explain and would hugely appreciate your help in understanding this. In the below screenshot I have some simple data in the grey area and some vlookups to extract part of this data using three array formulas denoted F1, F2 and F3. F1 looks up two items and returns one column which behaves as expected. F2 looks up one item and returns two columns which again behaves as expected. However, F3 which is essentially a combination of F1 and F2, should look up two items and return two columns but only the first column is returned. The below image shows what is being returned by Excel:

What I would be expecting from F3 is this:

I am aware that VLOOKUP suffers from many limitations and there are plenty of alternative formulas that could return the expected result. My question is not how to achieve this outcome as such but rather why VLOOKUP behaves the way it does in this situation. I believe it has something to do with array resistance and I have been trying various permutations on the solutions offered by EXELXOR in his article at Coercing array returns from CSE-resistant formulas without success. I would therefore massively appreciate if anyone could shed some light into the following questions to help my understanding on array formulas as a whole:

1. Why does an array in the first argument of a VLOOKUP seem to prevent an array from being recognised in the third argument of a VLOOKUP as per the above illustration?
2. Is there any way to coerce F3 to return what I am looking for i.e. a 2x2 matrix returning two columns for the two lookup values?
3. Is this a problem specific to VLOOKUPS (and HLOOKUPS) or are there other functions which exhibit this type of behaviour i.e. an array in one argument changing the way that an array in a different argument behaves.

Many thanks in advance to anyone looking into this or offering any kind of help!

 

[jasonb75]

I am aware that VLOOKUP suffers from many limitations and there are plenty of alternative formulas that could return the expected result.

I wouldn't call it a limitation, more like an expectation when you're trying to turn a goldfish into a unicorn. The alternatives are there to eliminate the need to attempt the impossible.

 

[benkarst]

Hi jasonb75,
Thanks for getting back to me on this. I appreciate your point and I would normally not use VLOOKUPs for this purpose but I am still puzzled by why it would behave like this in such a situation. If it is indeed impossible to use a VLOOKUP this way then that is fair enough but I would still like to understand why it is possible to turn it into an array formula manipulating arrays in both arguments 1 and 3 respectively but not possible to have arrays in both arguments at the same time. This is simply not something I have come across in any other function and it does make me wonder if there are other formulas that exhibit the same behaviour. I.e. from my previous understanding I would have said that any formula where F1 and F2 from my example produces a correct multi-cell array result would cope with F3 as well and any formula that cannot cope with F3 would not be able to cope with F1 and F2 either which does seem to hold true in any other situation I have come across so far. I would therefore hugely appreciate any technical insight into why VLOOKUPS seem to be able to deal with array formulas partially but not completely for educational purposes as it throws out what I thought to be true for array formulas in general.

 

[jasonb75]

Technical insight is not my thing, I'm a mere mortal with a bit of experience.

From things that I've tried, I would say that when a function is not meant to accept an array it can be coerced into doing so either vertically or horizontally, but not both at the same time.
Bearing in mind that VLOOKUP is a function that has been in excel for a long time, it was probably developed with current (at that time) system limitations in mind.

 

[benkarst]

Thanks for your help here jasonb75. I've done some experimenting and the different array directions do seem to cause the problem here and in similar functions so thanks for the insight.