Excel users have relied on lookup formulas for decades, but XLOOKUP is the modern function that makes many older formulas feel unnecessarily complicated. Whether you are matching customer names, finding prices, checking employee records, or building dashboards, XLOOKUP can search data in a cleaner, more flexible way than VLOOKUP or HLOOKUP.
TLDR: XLOOKUP searches for a value in one range and returns a related result from another range. For example, =XLOOKUP(“P-104”,A2:A50,D2:D50) can find product code P-104 and return its price from column D. In a sales team tracking 2,500 monthly orders, replacing manual searches with XLOOKUP could reduce lookup time by more than 80%. It is especially useful because it can look left, look right, handle missing values, and return exact matches by default.
What Is XLOOKUP?
XLOOKUP is an Excel function used to find information in a table or list. It looks for a specific value in one column or row, then returns a matching value from another column or row.
The 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 you want returned.
- if_not_found: Optional text or value to show if no match exists.
- match_mode: Optional setting for exact, approximate, or wildcard matching.
- search_mode: Optional setting for searching first-to-last, last-to-first, or using binary search.
The best part? XLOOKUP uses an exact match by default, which is often what users actually want. With VLOOKUP, forgetting to set exact match could lead to incorrect results.
Example 1: Find a Product Price
Imagine you have a product list with product codes in column A and prices in column C:
- A2:A100: Product Code
- B2:B100: Product Name
- C2:C100: Price
If cell E2 contains the product code you want to search for, use:
=XLOOKUP(E2,A2:A100,C2:C100)
If E2 contains P-215, Excel searches column A for P-215 and returns the matching price from column C. This is cleaner than VLOOKUP because you do not have to count column numbers. If the price column moves later, your formula is less likely to break.
Example 2: Show a Friendly Message When No Match Is Found
One common problem with lookup formulas is the ugly #N/A error. XLOOKUP handles this neatly with the optional if_not_found argument.
Using the same product table, try:
=XLOOKUP(E2,A2:A100,C2:C100,"Product not found")
If the product code is missing, Excel displays Product not found instead of an error. This is useful in reports shared with managers or clients, where a readable message looks more professional than a formula error.
Example 3: Look Left, Not Just Right
One of the biggest advantages of XLOOKUP over VLOOKUP is that it can return values from columns to the left of the lookup column.
Suppose your employee table has:
- A2:A200: Employee Name
- B2:B200: Department
- C2:C200: Employee ID
If you know the employee ID and want to return the employee name, use:
=XLOOKUP(F2,C2:C200,A2:A200,"ID not found")
Here, Excel searches column C but returns data from column A. With VLOOKUP, this would usually require rearranging the table or using a more complex INDEX and MATCH formula.
Example 4: Return Multiple Columns at Once
XLOOKUP can also return multiple related values in one formula. For example, if you search for an employee ID and want both the name and department, you can return columns A and B together.
=XLOOKUP(F2,C2:C200,A2:B200,"ID not found")
In Microsoft 365 and newer Excel versions, the results can spill into neighboring cells automatically. So one formula might return Maria Lopez in one cell and Finance in the next. This makes XLOOKUP excellent for summary pages, searchable forms, and dashboards.
Example 5: Use Wildcards for Partial Matches
XLOOKUP supports wildcard matching when you set the match mode to 2. This is useful when you only know part of a product name, customer name, or account label.
Suppose column B contains product names and column C contains prices. If G2 contains a partial word such as keyboard, use:
=XLOOKUP("*"&G2&"*",B2:B100,C2:C100,"No match",2)
The asterisks mean “anything before or after this text.” So Excel can match names like Wireless Keyboard Pro or Compact Keyboard Set. This can save time when product descriptions are long or inconsistent.
Example 6: Find the Last Matching Value
Sometimes you do not want the first match; you want the latest one. For example, a customer may place multiple orders, and you may need their most recent order amount.
If customer names are in A2:A1000 and order totals are in D2:D1000, use:
=XLOOKUP(H2,A2:A1000,D2:D1000,"No order",0,-1)
The final argument, -1, tells Excel to search from bottom to top. If your order list is sorted chronologically, this returns the customer’s latest order. That is powerful for sales tracking, customer service, and accounts receivable reports.
XLOOKUP vs VLOOKUP: Why Switch?
VLOOKUP is still widely used, but XLOOKUP solves several long-standing problems. Here are the biggest differences:
- No column counting: XLOOKUP uses a return range instead of a column index number.
- Looks in any direction: It can return values from the left, right, above, or below.
- Exact match by default: This reduces accidental incorrect results.
- Built-in error handling: You can define what appears when no match is found.
- Can return multiple columns: Great for modern dynamic Excel sheets.
For example, if a pricing team manages 10,000 SKUs, a VLOOKUP formula using column number 14 can easily break when someone inserts a new column. XLOOKUP avoids that risk because it points directly to the return range.
Common Mistakes to Avoid
XLOOKUP is easier than older lookup formulas, but a few mistakes are still common:
- Mismatched range sizes: The lookup array and return array must be compatible. For example, do not search A2:A100 and return C2:C80.
- Hidden extra spaces: If a lookup fails unexpectedly, check for spaces before or after text values.
- Wrong match mode: Use wildcard mode only when you need partial matches.
- Not locking ranges: When copying formulas, use absolute references like
$A$2:$A$100.
A Practical Business Scenario
Consider a small online retailer with a monthly sales file containing 4,800 orders. The operations manager wants to type an order ID into a search box and instantly see the customer name, delivery status, and total amount. With XLOOKUP, the workbook can use separate formulas to pull each field:
=XLOOKUP(K2,A2:A4801,B2:B4801,"Order not found")for customer name=XLOOKUP(K2,A2:A4801,E2:E4801,"Order not found")for delivery status=XLOOKUP(K2,A2:A4801,F2:F4801,"Order not found")for order total
Instead of filtering thousands of rows manually, the manager gets an instant result. Over a month, even saving two minutes per customer inquiry can add up to several hours of recovered productivity.
Final Thoughts
XLOOKUP is one of the most useful Excel functions for anyone who regularly works with lists, reports, or databases. It is readable, flexible, and less fragile than older lookup methods. Once you understand the basic pattern of search here, return from there, you can use it for pricing tables, employee directories, invoice tracking, inventory checks, and interactive dashboards. If you still rely on VLOOKUP, learning XLOOKUP is one of the fastest ways to make your spreadsheets more accurate and easier to maintain.