XLOOKUP Support

[qcurtemanjc]

Would it be possible to add the XLOOKUP function? I see there is VLOOKUP already, would be nice to have XLOOKUP as well.

Thank you.

[warpech]

XLOOKUP is surely interesting. One of the tricky parts about it, is that it's not part of the https://docs.oasis-open.org/office/OpenDocument/v1.3/OpenDocument-v1.3-part4-formula.html standard. So an implementation would require some compatibility research. However, it is spreading onto more and more spreadsheet software (Google Sheets added XLOOKUP last month).

It might be a while before we implement this function, because we prioritize bug and interoperability fixes before any new functions. Also, there are ~30 other standard (however, less popular) functions that HyperFormula is still missing.

This is an open-source project, and if someone wants to contribite an implementation of XLOOKUP, we will be very happy to work together. As it happened recently with #1047.

[selimyoussry]

Hi @warpech @qcurtemanjc, XLOOKUP is also quite important for me, there are several Excel files I can't work with because they use XLOOKUP extensively.

I am proposing in this PR: #1414 an implementation of XLOOKUP. Limitations

• Only standard match_mode
• Only standard search_mode.
• Per my understanding of Hyperformula, ranges can't be returned, so formulas like =XLOOKUP(...):XLOOKUP(...) wouldn't work

Even with these limitations, I'm hoping this covers 90% of use cases. I'm available to chat about it of course if this helps.

[sequba]

Issue: #1458

[AMBudnik]

Hi @qcurtemanjc @selimyoussry I am happy to announce that we just released HyperFomurmula v3.0.0 with the XLOOKUP function built-in. Thank you for your feedback!

If you'd like to read the full list of changes please visit https://hyperformula.handsontable.com/guide/release-notes.html#_3-0-0