search

Excel-DNA / XFunctions

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

xlookup add in doesn't work properly #8

Open VivienWW opened 2 years ago

VivienWW commented 2 years ago

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 image for example if my lookup value is single cell, it's working. if my lookup value is multi cells, it doesn't work image 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 image

I use microsoft 2019, I install for 64 image

please advise it

Thanks Vivien

govert commented 2 years ago

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 commented 2 years ago

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 commented 2 years ago

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 commented 2 years ago

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 commented 2 years ago

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.