xlookup add in doesn't work properly

[VivienWW]

Hi I have add in ExcelDna.xfunction version 5. only very basic function is working like use it as vlookup, most the function doesn't work for example if my lookup value is single cell, it's working. if my lookup value is multi cells, it doesn't work Also if i want to use more complicated xlookup function like search the next smaller value, it doesn't work via single cell or multi cells

I use microsoft 2019, I install for 64

please advise it

Thanks Vivien

[govert]

It might be that the XLOOKUP replacement function is returning the array, but if you don't select a target range of the right size and enter it as an array formula with Ctrl+Shift+Enter, the result will only show the first result item.

The Dynamic Arrays feature of new Excel is what make the XLOOKUP result automatically expand, and this feature is much harder to patch into an old Excel version. The XFunctions add-in is not trying to do Dynamic Arrays, it just does the two XMATCH and XLOOKUP functions.

[VivienWW]

Also if i want to use more complicated xlookup function like search the next smaller value, it doesn't work via single cell or multi cells

For this case， I manually selected the right area. Didn’t use ctrl + shift + entry. But always return as #value?. What did I do wrong? Thanks Vivien

[govert]

A single cell function looking for next smaller value should work fine, the same as the real XLOOKUP. Can you make up a small example where you have a problem, so I can test it?

[VivienWW]

Thank you Govert so much for your help.

This is the simple case. If I use Microsoft 365, it is working perfectly. If I use 2019 excel, it doesn't work at all

xlookup case.xlsx

Please advise it

Thanks Vivien

[govert]

OK I've had a first look. I see there are two bugs in my implementation, one is easy to fix, the other will need a bit more effort. The easy one is to do with the "-2" descending option, where I had a typo. The second has to do with the handling of empty cells. I still have to figure out what Excel is doing in this case - it is not consistent between MATCH and XMATCH. But at the moment the XFunctions replacement for XLOOKUP sometimes doesn't handle empty cells in the looked up array.

Thanks for showing me the problems. I'll post back here when I've pushed a fix.