Excel XLOOKUP finds a value in one range and returns the corresponding value from another range. For office work, it is especially useful for matching IDs across sheets, returning several fields at once, finding the latest entry in a list, and applying a threshold table. These five worked Excel XLOOKUP examples show the exact cell layouts, formulas, and expected results.
What is the XLOOKUP formula in Excel?
The basic formula is =XLOOKUP(lookup_value,lookup_array,return_array). The first argument is the item to find, the second is the range containing possible matches, and the third is the aligned range containing the answer. By default, XLOOKUP seeks an exact match and returns the first matching row. Unlike VLOOKUP, the answer column can be to the left or right of the lookup column.
The complete syntax is =XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode]). Microsoft documents the optional arguments and their behavior in its official XLOOKUP function reference. This guide uses comma separators; some regional Excel settings use semicolons instead. The examples apply to supported editions including Microsoft 365 and Excel 2021/2024, but XLOOKUP is not available in Excel 2016 or 2019.
Example 1: Match product codes across two worksheets
Imagine an order sheet with product code P-102 in cell A2. A separate worksheet named PriceList contains approved unit prices. You need the matching price without copying the entire price table into each order. Put the reference data in PriceList as follows:
| Cell A: Code | Cell B: Description | Cell C: Price |
|---|---|---|
| P-101 | Notebook | 12.00 |
| P-102 | Desk tray | 18.50 |
| P-103 | Pen set | 7.25 |
Enter this in the order sheet’s B2 cell: =XLOOKUP(A2,PriceList!$A$2:$A$4,PriceList!$C$2:$C$4,"Check code"). With A2 set to P-102, the result is 18.50. If the code is absent, the formula displays Check code instead of #N/A. That warning should trigger a source-data check, not an automatic price of zero.
The dollar signs lock the source ranges while you fill the formula down for other orders. If you need to copy it through a long column, the JSW guide to filling Excel formulas down efficiently explains the relevant shortcuts. For a growing source list, convert the reference range to an Excel Table and use structured references so new rows are included.
Example 2: Return a name from a column to the left
Suppose an employee directory has names in column A, departments in B, and employee IDs in C. Your search cell F2 contains E-202. Traditional VLOOKUP normally expects the lookup key in the leftmost column of its table array; XLOOKUP does not have that restriction.
| A: Name | B: Department | C: Employee ID |
|---|---|---|
| Alex Chen | Operations | E-201 |
| Priya Shah | Finance | E-202 |
| Sam Lee | Support | E-203 |
Use =XLOOKUP(F2,$C$2:$C$4,$A$2:$A$4,"Unknown ID"). Excel searches column C and returns the name from column A in the same row. The expected answer is Priya Shah. This is useful when an imported directory puts the identifier after descriptive fields and you do not want to rearrange approved source columns.
Check that IDs are unique before trusting a first-match result. If two people share the same code because of a bad import, XLOOKUP returns the first match rather than warning you about the duplicate. Resolve identity and access questions in the source directory, not by selecting an arbitrary row.
Example 3: Return two related fields with one formula
Sometimes a task register needs both the owner and current status for a selected request. On a sheet named Requests, place request IDs in A2:A4, owners in B2:B4, and statuses in C2:C4. Use these example rows: R-301 / Maya / Open; R-302 / Jordan / Waiting; R-303 / Lee / Closed. Enter R-302 in F2.
In G2, enter =XLOOKUP(F2,Requests!$A$2:$A$4,Requests!$B$2:$C$4,"Not found"). In a dynamic-array-enabled Excel edition, the result spills across G2:H2 as Jordan and Waiting. The return array is two columns wide, so a single lookup can retrieve both fields without two separate formulas.
Leave H2 empty before entering the formula. If a cell blocks the spill area, Excel may show #SPILL!. Do not overwrite a colleague’s manually maintained data merely to make the formula fit; place the output in a clear reporting area. For shared trackers, the related JSW article on documenting meaningful spreadsheet changes explains how to record adjustments to reference data and formulas.
Example 4: Find the last matching entry in an ordered log
An exception log may contain several rows for the same ticket as staff add updates. If you want the last matching row in the displayed range, use the optional search mode -1. This searches from the bottom upward; it does not independently compare timestamps.
| A: Ticket | B: Updated | C: Status |
|---|---|---|
| T-40 | Oct 12 | Open |
| T-41 | Oct 13 | Open |
| T-40 | Oct 14 | Review |
| T-40 | Oct 16 | Closed |
With T-40 in F2 and the table occupying rows 2–5, enter =XLOOKUP(F2,$A$2:$A$5,$C$2:$C$5,"No entry",0,-1). The result is Closed, because the bottommost T-40 row has that status. The 0 requests an exact match and -1 reverses the search direction.
This technique is reliable only if the row order represents the sequence you mean. If someone sorts the table alphabetically by status, the bottommost match may no longer be the most recent update. Maintain a dependable timestamp or sequence field and sort deliberately before using this pattern. Keep an audit trail when important status changes influence reporting.
Example 5: Find the applicable threshold in a rate table
Not every lookup is an exact ID match. An office purchasing policy might use volume thresholds to assign an internal planning discount. For a simplified example, enter thresholds in A2:A5 as 0, 100, 500, 1000, with corresponding rates in B2:B5 as 0%, 2%, 5%, 8%. Put an order quantity of 620 in F2.
Use =XLOOKUP(F2,$A$2:$A$5,$B$2:$B$5,"Below minimum",-1). The -1 match mode means exact match or next smaller value. Because 620 lies between 500 and 1000, the returned planning rate is 5%. The example rates are illustrative and are not an actual supplier offer.
Make threshold rules explicit: Does 500 mean at least 500 units? Are quantities whole numbers? Do discounts apply to the entire order or only units above a threshold? A correct formula cannot resolve an ambiguous commercial policy. Keep the reference table approved, visible, and clearly named, and do not replace a missing threshold with an invented percentage.
Why does XLOOKUP return #N/A, #VALUE!, or the wrong result?
#N/A: An exact match is absent, or values that look identical are not equal. Check spaces, text-versus-number types, and the actual source range. The optional if_not_found argument can display a useful message, but it should not conceal unresolved data problems.
#VALUE!: Verify that lookup and return arrays have compatible dimensions. For a vertical lookup, the lookup column and return range must cover the same number of rows. Confirm that references still point to the intended sheet after colleagues rename or move files.
Wrong but plausible result: Look for duplicate IDs, unintended approximate-match settings, or reversed log ordering. XLOOKUP defaults to the first exact match; a duplicate key can therefore produce a believable answer that is not the record you intended. For important reporting, compare several known IDs with their authoritative source entries before distributing the workbook.
#NAME?: Confirm that your Excel version supports XLOOKUP and that the function name is spelled correctly. Microsoft specifically notes that Excel 2016 and Excel 2019 do not provide the function. For colleagues using older editions, plan a compatible alternative such as INDEX with MATCH rather than sharing a workbook they cannot calculate.
How should you maintain XLOOKUP formulas in a shared workbook?
Use a stable unique key, label the source worksheet, and separate raw imported data from reporting outputs. Keep source and return ranges aligned, and choose a missing-match message that prompts a human to investigate. When formulas are copied, lock fixed ranges or use an Excel Table; otherwise a reference may shift to the wrong rows.
For repeatable handoffs, store the authoritative workbook in a clearly named location and note which sheet owns the reference data. The JSW guide to shared-folder file naming rules helps colleagues distinguish the current workbook from superseded exports. The accompanying Excel shortcuts cheat sheet is useful for navigating and checking large ranges.
Which XLOOKUP example should you use first?
Use the cross-sheet exact match for product or client IDs, the leftward lookup for directories, the two-column return for compact status reports, the reverse search for a log with dependable row order, and the next-smaller match for explicitly defined thresholds. Start with one known input and verify the expected answer before filling the formula down. A lookup is trustworthy when its source, matching rule, and missing-data behavior are understandable to the next person who opens the file.