Bugs On Complex Criteria

[Rakshith]

There are bugs in Xlookup in relating to Complex Criteria, I have used the formula "" =XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16) "" which is given in the website https://exceljet.net/formula/xlookup-with-complex-multiple-criteria, the result becomes #N/A in MS Office Excel 2019 Pro Plus, but the same formula working in online excel-onedrive, it gives the correct answer. Similarly Excel 2019 gives ans in the same row which is mysterious(8th row)

I have pasted Screenshot

Excel 2019

Online Excel-onedrive

[Govert van Drimmelen]

Thank you for the bug report – I’ll take a look.

 

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/79a2579a-b054-4f0a-960c-c48cee36b335n%40googlegroups.com.

[Govert van Drimmelen]

OK, I think I understand what is happening.

 

You are using the built-in functions LEFT and MONTH, which seem to be evaluated differently in Excel 2019 and new Dynamic-Arrays Excel.

If you press the Formula -> Evaluate Formula button you will see how the inner parts of the formulas behave differently.

I’m comparing Excel 2016 with Excel 365.

 

 

 

 

I’m not sure if there’s a way to get the array behaviour for built-in functions in the old Excel.

But it’s a good example that shows how some ways that you want to use XLOOKUP also depends on the Dynamic Arrays functionality, and I can’t fake that in old Excel.

 

It’s a nice example and I’ve made an issue on GitHub to remember this or at least document it.

See https://github.com/Excel-DNA/XFunctions/issues/7

 

I think the keywords to look for if you want to dig further would be “implicit intersection” and “dynamic arrays”  - you’ll find details on how this has changed.

Please post back if you find anything more.

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/002601d6b6d8%249ba596c0%24d2f0c440%24%40icon.co.za.

[Govert van Drimmelen]

Ah – it looks like you can get it to work by entering the formula with Ctrl+Shift+Enter, which disables implicit intersection.

 

Could you perhaps try that?

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/005e01d6b6df%24bdee1f70%2439ca5e50%24%40icon.co.za.