hyperformula icon indicating copy to clipboard operation
hyperformula copied to clipboard

Add XLOOKUP without support for non standard match_mode and search_mode - and returning values/arrays instead of ranges

Open selimyoussry opened this issue 1 year ago • 1 comments

Context

Per this discussion it seems like XLOOKUP is useful to a bunch of people. It is to for me at the very least :) and so I am happy to contribute to its implementation!

How did you test your changes?

I have tested the XLOOKUP implementation against the examples from Microsoft's official doc

  • [x] Example 1: should find value in simple column range
  • [x] Example 2: should find row range in table
  • [x] Example 2 transposed: should find column range in table (official example 2, transposed). This makes sure both column and row search work.
  • [x] Example 3: should find use if_not_found argument if not found
  • [ ] Example 4: current limitation of this implementation, the approximate match and non standard search are not supported. I added these limitations to the documentation. Hence, this example is not tested against.
  • [x] Example 5: nested xlookup function to perform both a vertical and horizontal match (official example 5)
  • [ ] Example 6: two nested xlookup + sum (official example 6). I wrote the code but then realized that (based on my shallow understanding of Hyperformula, please correct me if I am wrong), Hyperformula does not support functions returning ranges, only functions returning values.

Types of changes

  • [ ] Breaking change (a fix or a feature because of which an existing functionality doesn't work as expected anymore)
  • [x] New feature or improvement (a non-breaking change that adds functionality)
  • [ ] Bug fix (a non-breaking change that fixes an issue)
  • [ ] Additional language file, or a change to an existing language file (translations)
  • [ ] Change to the documentation

Related issues:

  • Related to this discussion https://github.com/handsontable/hyperformula/discussions/1064

Checklist:

  • [x] I have reviewed the guidelines about Contributing to HyperFormula and I confirm that my code follows the code style of this project.
  • [x] I have signed the Contributor License Agreement.
  • [ ] My change is compliant with the OpenDocument standard. This does not apply as XLOOKUP is not part of this standard
  • [x] My change is compatible with Microsoft Excel.
  • [x] My change is compatible with Google Sheets. Should be if the XLOOKUP implementation in Sheets is the same as Excel
  • [ ] I described my changes in the CHANGELOG.md file. That feels like it's up to you guys to see if that's releasable :)
  • [x] My changes require a documentation update.
  • [ ] My changes require a migration guide.

selimyoussry avatar Jun 15 '24 14:06 selimyoussry

Thank you for signing the CLA and sharing the pull request.

AMBudnik avatar Jun 18 '24 06:06 AMBudnik

Merging this PR into a local branch to fine-tune the implementation. Thank you, @selimyoussry, for the contribution.

sequba avatar Dec 05 '24 13:12 sequba

Thank you again for your input, @selimyoussry, and for spending your time improving HyperFormula. I'm glad to see the new function added in HF v3. Thank you!

AMBudnik avatar Jan 14 '25 12:01 AMBudnik