How to XLOOKUP from another sheet
✓ Verified in LibreOffice 25.8.7.3Do a modern XLOOKUP where the lookup table sits on a different tab.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =XLOOKUP(A2,Prices!A:A,Prices!B:B,"Not found") | Needs Excel 2021+/365. Lookup and return ranges both carry the sheet prefix. |
| Google Sheets | =XLOOKUP(A2,Prices!A:A,Prices!B:B,"Not found") | Identical. |
| LibreOffice Calc | =XLOOKUP(A2,Prices.A:A,Prices.B:B,"Not found") | LibreOffice 24.8+ (dot syntax). Older LibreOffice: use INDEX/MATCH across the sheet. |
How it works
XLOOKUP reaches another tab exactly like VLOOKUP does — prefix the lookup and return ranges with the sheet name — but with XLOOKUP's advantages: exact match by default, a built-in not-found result, and no column-number counting. "cherry" on the Prices sheet returns 30. XLOOKUP needs Excel 2021+/365, current Google Sheets, or LibreOffice 24.8+; in older versions use INDEX/MATCH with the same sheet-prefixed ranges. Remember LibreOffice's dot separator (Prices.A1). Verified against a real second tab.
Verified, not just documented
We ran =XLOOKUP(A2,Prices!A1:A3,Prices!B1:B3,"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.