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:

- 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?
- 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?
- 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.