XLOOKUP Basic Exact Match: How to Use It

XLOOKUP with an exact match finds a value in one column and returns a matching value from another column, requiring the lookup value to be identical to the cell content. You enter the lookup value, the range to search, and the range to return from, and you get the corresponding result or a custom message if nothing matches.

When to Use XLOOKUP Exact Match

Use basic exact match when you need to retrieve data that must match exactly, such as product codes, employee IDs, email addresses, or part numbers. It is ideal for lookup tables where each key appears once. Unlike VLOOKUP, XLOOKUP can look to the left (return a column that is to the left of the lookup column) and does not require the lookup column to be the first column of a table. It also handles missing values gracefully with the if_not_found argument.

Exact match is the default mode, so you don’t need to specify anything extra. If your data contains duplicates, XLOOKUP returns the first match (unless you set search_mode to -1 for last match). This is usually what you want for unique IDs.

Common Mistakes and How to Avoid Them

• Mismatched range sizes: The lookup_array and return_array must have the same number of rows (for vertical lookups) or columns (for horizontal). If they differ, XLOOKUP returns #VALUE!.
• Hidden spaces or non-printing characters: A value like “102 ” (with a trailing space) will not match 102. Use TRIM() or CLEAN() on your data if needed.
• Number vs. text formatting: A number stored as text (e.g., “102”) won’t match a numeric 102. Use the VALUE() function or format both consistently.
• Forgetting the if_not_found argument: Without it, missing values show #N/A, which can be confusing. Always provide a friendly message like “Not found” or “Check ID”.
• Using approximate match unintentionally: If you omit match_mode, it’s exact. But if you set match_mode to 1 or -1, you get approximate matches. Double-check your formula if you see unexpected results.

XLOOKUP vs. VLOOKUP vs. INDEX/MATCH

XLOOKUP is the modern replacement for VLOOKUP and INDEX/MATCH. It is simpler, more flexible, and faster in many cases. Here’s a quick comparison:

• VLOOKUP: Requires the lookup column to be the first column of the table, can only return columns to the right, and defaults to approximate match (which can cause errors if not set to FALSE). It also breaks if you insert a column in the table.
• INDEX/MATCH: More flexible than VLOOKUP (can look left, works with any order), but requires two functions and is harder to read. XLOOKUP does the same job with one function.
• XLOOKUP: Handles left lookups, defaults to exact match, supports if_not_found, and can return entire rows or columns. It also works with spilled arrays in modern Excel.

If you are using Excel 2021 or Microsoft 365, XLOOKUP is the recommended choice. For older versions (Excel 2019 and earlier), you need VLOOKUP or INDEX/MATCH.

Real-World Examples and Tips

Here are a few practical applications:

• Employee directory: Look up an employee ID to return their email or department.
• Product pricing: Look up a product SKU to return its price or stock level.
• Grade lookup: Look up a student’s name to return their grade (exact match on name).
• Cross-referencing two lists: Use XLOOKUP to pull data from a master sheet into a report.

Tip: Use cell references for the lookup value (e.g., =XLOOKUP(D2, A:A, B:B)) so you can drag the formula down and apply it to many rows. Also, you can use entire columns (A:A) as ranges, but be aware that this may slow down calculations on very large datasets.

Limitations and When Not to Use It

XLOOKUP is powerful, but it has a few limitations:

• Not available in older Excel versions: If you share files with users on Excel 2019 or earlier, they will get #NAME? errors.
• Case-insensitive by default: It does not distinguish between uppercase and lowercase. If you need case-sensitive matching, use EXACT() with INDEX/MATCH or a helper column.
• Performance on massive datasets: While XLOOKUP is fast, using whole-column references on millions of rows can slow things down. Use a specific range like A2:A1000 if possible.
• Duplicate lookup values: It returns the first match (or last with search_mode -1). If you need all matches, use FILTER instead.

For case-sensitive lookups, you can combine XLOOKUP with EXACT like this: =XLOOKUP(1, --EXACT(A2:A10, D2), B2:B10) – this is an array formula that works in Excel 365.

Frequently Asked Questions

How do I use XLOOKUP for an exact match?

Simply enter =XLOOKUP(lookup_value, lookup_range, return_range) and leave the match_mode argument empty (or set it to 0). The default is exact match, so no extra argument is needed.

What does XLOOKUP return if no match is found?

By default, it returns #N/A. To show a custom message, add a fourth argument: =XLOOKUP(value, range, return, "Not found").

Can XLOOKUP look to the left?

Yes. Unlike VLOOKUP, the return range can be any column, including those to the left of the lookup range. For example, =XLOOKUP(B2, C:C, A:A) looks up in column C and returns from column A.

Is XLOOKUP case-sensitive?

No, by default it is case-insensitive. For case-sensitive matching, you need a workaround using EXACT and array formulas, or use a helper column with the EXACT function.

Why is my XLOOKUP returning #VALUE!?

This usually happens when the lookup_array and return_array are not the same size. Check that both ranges have the same number of rows (or columns for horizontal lookups).

Can I use wildcards with XLOOKUP?

Yes, but only if you set match_mode to 2. For example, =XLOOKUP("*abc*", A:A, B:B, , 2) finds the first cell containing “abc”. Note that wildcards only work with text, not numbers.

Related

• Zoom End-to-End Encryption: How It Works
• AI Agent Monitoring: What to Measure in Production
• EVO Brazil Packing Checklist: Competitors & Spectators