How to use XLOOKUP in Excel
Formula (Excel and Google Sheets)
=XLOOKUP(G1,A2:A9,D2:D9)Give XLOOKUP the value to find, the range to search and the range to return: =XLOOKUP(G1,A2:A9,D2:D9). It looks for an exact match by default.
- Result on the example
- 188 in
G2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in G2. Change any input and the result updates, or edit the formula to experiment.
How it works
XLOOKUP takes three arguments: what to find (G1), where to look (A2:A9) and what to bring back (D2:D9). It finds HX-220 in the 3rd row of the search range and returns the 3rd value of the return range, 188.
Exact match is the default, so there is no FALSE to forget, and because the two ranges are separate, the return column can sit on either side of the search column.
=XLOOKUP(G1,A2:A9,D2:D9)Result188
G1"HX-220"The value in G1.
A2:A98 rows × 1 col: "HX-114", "HX-162", "HX-220", "WS-108", …A range: 8 cells in one column.
D2:D98 rows × 1 col: 420, 315, 188, 960, …A range: 8 cells in one column.
XLOOKUP(G1,A2:A9,D2:D9)188XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
G1"HX-220" - lookup_array
A2:A98 rows × 1 col: "HX-114", "HX-162", "HX-220", "WS-108", … - return_array
D2:D98 rows × 1 col: 420, 315, 188, 960, …
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =XLOOKUP(G1,A2:A9,D2:D9). We ran it in Google Sheets mode on the same data and it returns 188.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =VLOOKUP(G1,A2:D9,4,FALSE) | 188 | Works in every Excel version. The 4 counts columns from the left edge of the table, and FALSE asks for an exact match. |
| =INDEX(D2:D9,MATCH(G1,A2:A9,0)) | 188 | The classic pairing for Excel 2019 and earlier when the result column is to the left of the search column. |
Common mistakes
- The search range and the return range must have the same number of rows.
A2:A9withD2:D10returns #VALUE!. - XLOOKUP needs Excel 2021, Microsoft 365, Excel for the web or Google Sheets. Excel 2019 and earlier show #NAME?.
- A missing part returns #N/A. Add a fourth argument for a message:
=XLOOKUP(G1,A2:A9,D2:D9,"Not found").
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Use XLOOKUP
G2, use XLOOKUP to return the quantity on hand for the part number in G1.Questions
What are the optional arguments?
The 4th is what to show when nothing matches, the 5th is the match mode (0 exact, -1 exact or next smaller, 1 exact or next larger, 2 wildcards) and the 6th is the search direction (-1 searches from the bottom up).
Does XLOOKUP work in Google Sheets?
Yes, with the same arguments: =XLOOKUP(G1,A2:A9,D2:D9).
Can XLOOKUP return more than one column?
Yes. Give it a wider return range, like =XLOOKUP(G1,A2:A9,B2:D9), and the result spills across.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.