Excel XLOOKUP Basics: A Standard Lookup Function Easier Than VLOOKUP

This guide covers Excel XLOOKUP basics from a beginner’s perspective. You can reproduce exact matches, approximate matches, wildcards, left lookups, and multiple criteria by copying and pasting the tables and formulas.

Quick Fix: 3-Minute Recipe

1. Convert the source data to tables with Ctrl+T (PriceList, Items).
2. Unit price: =XLOOKUP([@Item], PriceList[Item], PriceList[Price], "Not found")
3. Amount: =IFERROR([@Qty]*[@[Unit Price]], 0)
4. See the examples below for left lookups, approximate matches, and wildcards.

XLOOKUP Basics

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

• Supports both left and right lookups, includes built-in error handling, and is safe when columns are inserted

Sample Data

Exact Match & Error Replacement

=XLOOKUP([@Item], PriceList[Item], PriceList[Price], "Not found")

=IFERROR([@Qty]*[@[Unit Price]], 0)

Left Lookup

=XLOOKUP("Pro", PriceList[Tier], PriceList[Name], "Not found")

Approximate Match

=XLOOKUP([@Qty], TierTable[MinQty], TierTable[UnitPrice], , -1)

Wildcards

=XLOOKUP("*Shirt*", PriceList[Name], PriceList[Item], "Not found", 2)

Multiple Criteria

=LET(
  key, TEXT([@Date],"yyyy-mm-dd") & "|" & [@Item],
  XLOOKUP(key,
          TEXT(Sales[Date],"yyyy-mm-dd") & "|" & Sales[Item],
          Sales[Price],
          "Not found")
)

Alternatives, Precautions, and Checklist

• Use Ctrl+T tables and column-name references to stay safe when columns are inserted
• Approximate matches require the lookup column to be sorted
• For duplicate values, use search_mode=-1 to find the last value

Troubleshooting

Conclusion

With these Excel XLOOKUP basics, you can perform lookups safely and simply. The next article covers advanced XLOOKUP techniques.