search

Excel-DNA / XFunctions

XLOOKUP and XMATCH for all Excel versions
MIT License
40 stars 21 forks source link

Document behaviour of built-in functions with complex criteria #7

Open govert opened 3 years ago

govert commented 3 years ago

See https://exceljet.net/formula/xlookup-with-complex-multiple-criteria and https://groups.google.com/g/exceldna/c/bwXlP-OXfPM

If seems that a formula like

=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16)

is evaluated differently in Dynamic-Array aware Excel vs older Excel. In particular, the LEFT and MONTH functions don't return arrays to the formula in the older Excel, but instead only the interpolated row / column value.

It would be good to sort this out and document a bit. Keywords would be "implicit intersection" and "dynamic arrays".

govert commented 3 years ago

It looks like it works right with 'implicit intersection' disabled, which happens when the formula is entered with Ctrl+Shift+Enter.

Should we automatically do this (together with resize?)