How to XLOOKUP from another sheet
✓ Verified in LibreOffice 25.8.7.3 ✓ Verified in Google Sheets (2026-08-30)Do a modern XLOOKUP where the lookup table sits on a different tab.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel (desktop) | =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. We then ran the same formulas in Google Sheets, executed 2026-08-30: a formula-only workbook goes into Google Drive, which converts it to a Sheet and recalculates every formula with Google’s own engine, and comes back out as .xlsx carrying the values Google computed. It returned 30 for the worked example, the same value LibreOffice produced. Both engines’ numbers on this page are executed results. The Excel formula follows Microsoft’s official documented syntax — we do not run desktop Excel.
Functions used
XLOOKUP — see full Excel, Google Sheets & LibreOffice compatibility for each.
Related recipes
- How to use XLOOKUP
- How to look up the LAST matching value (not the first)
- How to look up a value with two criteria (multi-condition lookup)
- How to reference a cell on another sheet
- How to return a default value when a lookup finds nothing