Excel XLOOKUP: Complete Guide with 20 Practical Examples

Excel’s XLOOKUP is the modern replacement for older lookup formulas like VLOOKUP, HLOOKUP, and many INDEX MATCH combinations. It is easier to read, works left or right, searches vertically or horizontally, and includes built-in options for missing values, approximate matches, and reverse searches.

TLDR: XLOOKUP helps you find a value in one range and return a related value from another range with one clean formula. For example, =XLOOKUP("P102",A2:A100,D2:D100) can find product P102 and return its price instantly. In a sales team tracking 5,000 product rows, replacing manual searches with XLOOKUP could cut lookup time by over 80%. It is especially useful for reports, dashboards, inventory sheets, and finance models.

What Is XLOOKUP?

XLOOKUP searches for a value in a lookup array and returns a matching result from a return array. Its basic syntax is:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

• lookup_value: The value you want to find.
• lookup_array: The range where Excel should search.
• return_array: The range containing the result.
• if_not_found: Optional message if no match exists.
• match_mode: Exact, approximate, or wildcard matching.
• search_mode: Search first-to-last, last-to-first, or binary search.

Unlike VLOOKUP, XLOOKUP does not require the return column to be to the right of the lookup column. That single improvement makes it much more flexible for real-world spreadsheets.

20 Practical XLOOKUP Examples

1. Basic product price lookup:
   =XLOOKUP("Laptop",A2:A20,B2:B20)
   Finds “Laptop” in column A and returns its price from column B.
2. Lookup using a cell reference:
   =XLOOKUP(E2,A2:A100,C2:C100)
   Searches for the value typed in E2 and returns the matching result from column C.
3. Return a custom message when not found:
   =XLOOKUP(E2,A2:A100,C2:C100,"Item not found")
   This avoids confusing #N/A errors in reports.
4. Look left instead of right:
   =XLOOKUP(D2,D2:D100,A2:A100)
   If column D contains employee IDs and column A contains names, XLOOKUP can return the name even though it is to the left.
5. Find an employee department:
   =XLOOKUP("E1045",A2:A500,D2:D500)
   Great for HR sheets where employee IDs link to teams, managers, or office locations.
6. Retrieve multiple columns at once:
   =XLOOKUP(G2,A2:A100,B2:D100)
   Returns several fields, such as product name, price, and stock level, in one formula.
7. Horizontal lookup:
   =XLOOKUP("March",B1:M1,B2:M2)
   Finds “March” across a row and returns the related value from another row.
8. Exact match only:
   =XLOOKUP(F2,A2:A100,B2:B100,"Not found",0)
   The 0 match mode tells Excel to return only an exact match.
9. Approximate match for tax rates:
   =XLOOKUP(B2,F2:F6,G2:G6,,1)
   Finds the next larger match, useful when tax brackets or discount thresholds are arranged in bands.
10. Approximate match for commission tiers:
    =XLOOKUP(C2,H2:H7,I2:I7,,-1)
    Finds the next smaller match, ideal for sales commission rates based on revenue levels.

11. Wildcard search:
    =XLOOKUP("*chair*",A2:A100,B2:B100,"No match",2)
    The 2 match mode enables wildcards, so Excel can find product names containing “chair.”
12. Search from bottom to top:
    =XLOOKUP(E2,A2:A100,C2:C100,"Not found",0,-1)
    This returns the latest matching record when newer entries are lower in the table.
13. Find the latest price for a product:
    =XLOOKUP("P200",A2:A100,D2:D100,,0,-1)
    Useful when the same product appears multiple times as prices change over time.
14. Lookup by date:
    =XLOOKUP(H2,A2:A365,C2:C365)
    Searches for a date and returns sales, attendance, exchange rates, or other daily values.
15. Find the closest previous date:
    =XLOOKUP(H2,A2:A365,C2:C365,,-1)
    Helpful when weekend or holiday dates do not exist in your dataset.
16. Nested XLOOKUP for two-way lookup:
    =XLOOKUP(H2,A2:A10,XLOOKUP(I2,B1:F1,B2:F10))
    Finds a value at the intersection of a selected row and column, such as region by month.
17. Combine XLOOKUP with SUM:
    =SUM(XLOOKUP("Q1",B1:E1,B2:E10))
    Returns a full column or row of values and sums them for quick quarterly reporting.
18. Use XLOOKUP with structured tables:
    =XLOOKUP([@Product],Table1[Product],Table1[Price])
    Structured references make formulas easier to understand and maintain.
19. Return a blank instead of an error:
    =XLOOKUP(E2,A2:A100,B2:B100,"")
    This keeps dashboards clean when optional inputs are empty or incomplete.
20. Lookup across sheets:
    =XLOOKUP(A2,Products!A:A,Products!D:D,"Missing")
    Searches the Products sheet and returns a value to your current report sheet.

Why XLOOKUP Is Better Than VLOOKUP

XLOOKUP improves on VLOOKUP in several important ways. It defaults to an exact match, so there is less risk of accidentally returning the wrong result. It can look left, return multiple columns, search from the bottom, and handle missing values without wrapping the formula in IFERROR.

For example, a VLOOKUP formula may break when someone inserts a new column because it relies on a fixed column number. XLOOKUP uses actual return ranges, so your formula is usually more stable and easier to audit.

Best Practices for Using XLOOKUP

• Use named ranges or tables for clearer formulas.
• Always add an if_not_found message in shared workbooks.
• Keep lookup and return arrays the same size to avoid errors.
• Sort data only when using binary search or certain approximate workflows.
• Use exact match for IDs, names, and codes unless approximate matching is intentional.

Common XLOOKUP Mistakes

The most common mistake is selecting lookup and return ranges with different dimensions. For instance, searching A2:A100 but returning from B2:B90 will cause problems. Another issue is using approximate match when exact match is required, especially with product codes, invoice numbers, or employee IDs.

Also remember that XLOOKUP returns the first match by default. If your dataset contains duplicates and you need the most recent result, use the search mode -1 to search from the bottom upward.

Final Thoughts

XLOOKUP is one of the most valuable Excel functions for anyone who works with data. Whether you are matching invoices, building dashboards, checking inventory, analyzing sales, or cleaning reports, it provides a faster and clearer way to connect information across tables. Once you learn the examples above, you can replace many older lookup methods with a formula that is both powerful and easy to understand.