MacOs Numbers and tricks with braces

macosnumbers

Has anybody played around with using braces in a formula? Syntactically, they seem to define collections inline in a formula. It would be great if we could figure out how to define multi-row collections. My apologies for not making this inline code but I think the paste is clearer.

But to give an idea of what I am talking about, the following will produce 2.

MATCH(TRUE,{FALSE,TRUE,TRUE},0)

Spreadsheet snippet of examples of using braces

Best Answer

Well, thank you Solar Mike, looking at Excel documentation gave me the answer to my question about how to define an inline collection with rows and columns.

My mistake was trying to use C++ array initializer syntax. Instead you use a semicolon like so:

=ROWS({FALSE,4;TRUE,5;TRUE,6})
=COLUMNS({FALSE,4;TRUE,5;TRUE,6})
=VLOOKUP(TRUE,{FALSE,4;TRUE,5;TRUE,6},2,0)

The first statement produces 3, because the semicolons signify a new row. The second statement produces 2, because there are two comma-separated elements between each semicolon and commas signify a new column. And finally the VLOOKUP produces 5, the value of the element in the second column of the second row which is returned by VLOOKUP.

Et voilĂ !