CODEWITHSUNDEEP
excellocalizationexcel-2007excel-formula
PPC
PPC

Reputation: 1923

MS Excel localization issue for "union" operator in formulas

I'm trying to build up an excel formula that will make a lookup in several arrays located on different sheets. So I'm trying to concatenate all arrays into one and make the lookup in this using the union operator to join the arrays. This would look like

=SUMPRODUCT(Sheet0!A1:A10,Sheet1!A1:A10 ; array2 ...)

Can't tell if this formula works because my windows box is configured with FR_CA locale that uses semicolon as list separator (at least this is my best guess), and any occurrence of a comma throws an error box (invalid formula).

I tried to use named ranges to work the problem around, at best my named ranges returned #VALUE

Note 1: I'm trying as much as I can to NOT use VBA Note 2: I've even tried to change the windows "list separator" preference back to comma, no change

So can anyone help with any of the 3 issues:

Upvotes: 1

Views: 1371

Answers (1)

lori_m
lori_m

Reputation: 5567

The union operator doesn't return an array, it returns a range consisting of multiple areas, and the individual ranges must be on the same sheet. The union operator symbol is the same as the function argument separator (i.e. a comma in English locales or semicolon in European locales) and references need to be enclosed by parentheses if placed within function arguments. For example this returns the sum of B1:B10:

=SUM(INDEX((Sheet1!A1:A10,Sheet1!B1:B10),,,2))

For concatenating ranges consisting of single columns to arrays you can use the syntax:CHOOSE({1,2,...},Col1,Col2,...), in function arguments e.g.:

=SUMPRODUCT(--(CHOOSE({1,2},Sheet1!A1:A10,Sheet2!A1:A10)="abc"),
               CHOOSE({1,2},Sheet1!B1:B10,Sheet2!B1:B10))

Upvotes: 2

Related Questions