I am experiencing problems with a vlookup array in Excel, O365.
My current Excel version where array vlookups are broken:
I have tested on a colleague's PC where array vlookups work correctly:
Note that I have set the list separator to a pipe symbol, but the problem also occurs if the list separator is a semi-colon.
To demonstrate:
Firstly;
An array vlookup eg =VLOOKUP(A3|Ref!A3:D4|{2,3}|FALSE) returns duplicate values of the first return value in consecutive cells for the same row: ie the "b" and "c" in col3 should be different, not repeats of col2
Secondly;
When an attempt is made to return more than two values in the array eg =VLOOKUP(A2|Ref!A2:D4|{2,3,4}|FALSE), then Excel no longer recognises the array formula
The Ref sheet is as follows:
Standard vlookup (one return value) works properly.
Lastly;
When setting the Excel decimal separator to a period instead of a comma, the list separator automatically gets set to a comma for formulas and then the array vlookup works correctly.
However, I have used the pipe list separator in Excel for the last 5 years and never had this problem with array vlookups before.
So it appears that the latest build (15128.20210) of O365 Excel doesnt handle non-comma list separators correctly, where previous builds had no problem.