How to use XLOOKUP
✓ Verified in LibreOffice 25.8.7.3The modern lookup — exact match by default, looks in any direction, and handles not-found gracefully.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =XLOOKUP(D2,A2:A10,B2:B10,"Not found") | lookup value, lookup range, return range, [if not found]. Needs Excel 2021+/365. |
| Google Sheets | =XLOOKUP(D2,A2:A10,B2:B10,"Not found") | Identical. |
| LibreOffice Calc | =XLOOKUP(D2,A2:A10,B2:B10,"Not found") | Needs LibreOffice 24.8+; earlier versions use INDEX/MATCH. |
How it works
XLOOKUP takes the value to find, the range to search, and the range to return from as three separate ranges — so unlike VLOOKUP there's no column-counting and the lookup and return columns can be in any order (it looks left just as easily as right). "cherry" is found in A2:A4 and 30 is returned from the matching row of B2:B4. The optional fourth argument is the not-found result ("Not found" here) instead of an ugly #N/A. It also defaults to exact match, so there's no FALSE to remember. XLOOKUP needs a modern version (Excel 2021+/365, current Google Sheets, or LibreOffice 24.8+); on older ones, INDEX/MATCH does the same job.
Verified, not just documented
We ran =XLOOKUP(D2,A2:A4,B2:B4,"Not found") in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 30 — exactly the expected result. Every formula here is confirmed by actually executing it.