User Profile
PaulJ68
Copper Contributor
Joined 3 years ago
User Widgets
Recent Discussions
Excel array vlookup not working when using list separators other than comma
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.
