Excel array vlookup not working when using list separators other than comma

%3CLINGO-SUB%20id%3D%22lingo-sub-3365128%22%20slang%3D%22en-US%22%3EExcel%20array%20vlookup%20not%20working%20when%20using%20list%20separators%20other%20than%20comma%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3365128%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20experiencing%20problems%20with%20a%20vlookup%20array%20in%20Excel%2C%20O365.%3C%2FP%3E%3CP%3EMy%20current%20Excel%20version%20where%20array%20vlookups%20are%20broken%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22PaulJ68_3-1652361770976.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F371160i49550BD0ED35E337%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22PaulJ68_3-1652361770976.png%22%20alt%3D%22PaulJ68_3-1652361770976.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20have%20tested%20on%20a%20colleague's%20PC%20where%20array%20vlookups%20work%20correctly%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22PaulJ68_4-1652361825545.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F371161i4CDCE710BCDF86C2%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22PaulJ68_4-1652361825545.png%22%20alt%3D%22PaulJ68_4-1652361825545.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENote%20that%20I%20have%20set%20the%20list%20separator%20to%20a%20pipe%20symbol%2C%20but%20the%20problem%20also%20occurs%20if%20the%20list%20separator%20is%20a%20semi-colon.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20demonstrate%3A%3C%2FP%3E%3CP%3EFirstly%3B%3C%2FP%3E%3CP%3EAn%20array%20vlookup%20eg%20%3CSTRONG%3E%3DVLOOKUP(A3%7CRef!A3%3AD4%7C%7B2%2C3%7D%7CFALSE)%3C%2FSTRONG%3E%20returns%20duplicate%20values%20of%20the%20first%20return%20value%20in%20consecutive%20cells%20for%20the%20same%20row%3A%20ie%20the%20%22b%22%20and%20%22c%22%20in%20col3%20should%20be%20different%2C%20not%20repeats%20of%20col2%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22PaulJ68_0-1652360024618.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F371152i8F87EF5DF205B5C4%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22PaulJ68_0-1652360024618.png%22%20alt%3D%22PaulJ68_0-1652360024618.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESecondly%3B%3C%2FP%3E%3CP%3EWhen%20an%20attempt%20is%20made%20to%20return%20more%20than%20two%20values%20in%20the%20array%20eg%20%3CSTRONG%3E%3DVLOOKUP(A2%7CRef!A2%3AD4%7C%7B2%2C3%2C4%7D%7CFALSE)%3C%2FSTRONG%3E%2C%20then%20Excel%20no%20longer%20recognises%20the%20array%20formula%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22PaulJ68_1-1652360214376.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F371153i9ED93C1CAC3DB27B%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22PaulJ68_1-1652360214376.png%22%20alt%3D%22PaulJ68_1-1652360214376.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThe%20Ref%20sheet%20is%20as%20follows%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22PaulJ68_2-1652360297044.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F371154i99812B12A95F5AD1%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22PaulJ68_2-1652360297044.png%22%20alt%3D%22PaulJ68_2-1652360297044.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EStandard%20vlookup%20(one%20return%20value)%20works%20properly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELastly%3B%3C%2FP%3E%3CP%3EWhen%20setting%20the%20Excel%20decimal%20separator%20to%20a%20period%20instead%20of%20a%20comma%2C%20the%20list%20separator%20automatically%20gets%20set%20to%20a%20comma%20for%20formulas%20and%20then%20the%20array%20vlookup%20works%20correctly.%3C%2FP%3E%3CP%3EHowever%2C%20I%20have%20used%20the%20pipe%20list%20separator%20in%20Excel%20for%20the%20last%205%20years%20and%20never%20had%20this%20problem%20with%20array%20vlookups%20before.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20it%20appears%20that%20the%20latest%20build%20(15128.20210)%20of%20O365%20Excel%20doesnt%20handle%20non-comma%20list%20separators%20correctly%2C%20where%20previous%20builds%20had%20no%20problem.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3365128%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

I am experiencing problems with a vlookup array in Excel, O365.

My current Excel version where array vlookups are broken:

PaulJ68_3-1652361770976.png

I have tested on a colleague's PC where array vlookups work correctly:

PaulJ68_4-1652361825545.png

 

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

PaulJ68_0-1652360024618.png

 

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

PaulJ68_1-1652360214376.png

The Ref sheet is as follows:

PaulJ68_2-1652360297044.png

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.

0 Replies