hyperformula
hyperformula copied to clipboard
Add XLOOKUP without support for non standard match_mode and search_mode - and returning values/arrays instead of ranges
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.
Thank you for signing the CLA and sharing the pull request.
Merging this PR into a local branch to fine-tune the implementation. Thank you, @selimyoussry, for the contribution.
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!