How to Use XLOOKUP for an Exact Match in Excel

Why exact matching matters

A lookup formula should return the record that matches the selected key—not merely the nearest value in a sorted list. Exact matching is especially important for employee IDs, account codes, invoice numbers, stock keeping units, and other identifiers where a similar value belongs to a different record.

XLOOKUP uses exact matching by default, which makes it safer than older lookup methods that could silently return an approximate result when the final argument was omitted. Even so, adding an explicit match mode of 0 can make the formula easier for another workbook user to audit.

Understand the XLOOKUP structure

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

Argument	Purpose
lookup_value	The code, name, or value you want to find.
lookup_array	The single row or column that contains the possible matches.
return_array	The row or column containing the result to return.
if_not_found	An optional message or value when no exact match exists.
match_mode	Use 0 for an explicit exact match.
search_mode	Controls whether Excel searches from the first or last item and whether a binary search is used.

1. Prepare a clean lookup table

Start with one row per record and a lookup column that contains the key. In an employee list, column A might contain Employee ID, column B the employee name, column C the department, and column D the hourly rate. The lookup key should be consistent and, when the business rule requires it, unique. For imported text cleanup before a lookup, use the extra-spaces and hidden-characters guide.

• Keep the lookup values in one continuous row or column.
• Use the same data type for the lookup cell and source values.
• Remove accidental spaces or hidden characters from imported text.
• Confirm that duplicate keys are intentional before relying on the result.
• Convert the source range to an Excel Table when it will grow over time.

2. Build the exact-match formula

Assume the employee ID to find is entered in H2. The source IDs are in A2:A8, and the department to return is in C2:C8. A clear exact-match formula is:

=XLOOKUP(H2,$A$2:$A$8,$C$2:$C$8,"Not found",0)

Excel searches A2:A8 for the value in H2. When it finds an exact match, it returns the value from the same row in C2:C8. If the ID is missing, the formula returns “Not found” instead of displaying #N/A. The dollar signs keep the source ranges fixed if the formula is copied.

3. Handle missing values without hiding real problems

The if_not_found argument is useful for user-facing forms and reports. Choose a message that tells the user what happened. “Not found” is usually clearer than a blank because it distinguishes a missing record from a formula that has not been completed.

Do not use the message to avoid investigating why a key is missing. Check for typing mistakes, extra spaces, leading zeros, text-versus-number differences, and outdated source data. A clean message improves the worksheet, but the source issue still needs review.

4. Return more than one column

In current Microsoft 365 versions, XLOOKUP can return several adjacent columns at once. To return the employee name, department, and rate from columns B:D, use:

=XLOOKUP(H2,$A$2:$A$8,$B$2:$D$8,"Not found",0)

The results spill into neighboring cells. Keep the destination area empty, or Excel will return a #SPILL! error. Use this approach when the fields belong together and the worksheet has enough space. For a single report column, returning only the needed field is usually easier to maintain.

5. Use structured references in an Excel Table

If the source is an Excel Table named tblEmployees, the formula can use field names instead of fixed coordinates:

=XLOOKUP(H2,tblEmployees[Employee ID],tblEmployees[Department],"Not found",0)

Structured references are easier to read and expand automatically when new rows are added. Confirm the table name and column headings exactly; a renamed heading will update normal structured formulas, but a copied formula from another workbook may refer to a table that does not exist.

6. Test the lookup with matching and nonmatching cases

1. Enter a key that you know exists and compare the result with the source row.
2. Enter a key that does not exist and confirm the “Not found” message.
3. Test a value with a leading zero if the source uses text identifiers.
4. Copy the formula where required and confirm that the lookup cell changes while the source arrays stay fixed.
5. Add a new source row when using a Table and confirm the lookup includes it.

Common XLOOKUP problems

Problem	What to check
Valid key returns “Not found”	Compare data types, spaces, hidden characters, and leading zeros.
Wrong record is returned	Look for duplicate keys and confirm the lookup array is the correct column.
#VALUE! appears	Confirm the lookup and return arrays have compatible dimensions.
#SPILL! appears	Clear the cells needed for a multi-column result.
Formula breaks when copied	Lock fixed source ranges or use an Excel Table.
XLOOKUP is unavailable	The Excel version may not support the function; use INDEX and MATCH or another compatible method. Continue with the INDEX-and-MATCH lookup guide when the workbook must support older lookup methods.

Final XLOOKUP verification checklist

• The lookup cell and source key use compatible data types.
• The lookup array contains the intended key field.
• The return array aligns with the same source rows.
• The exact-match behavior is clear.
• The missing-value message is useful and not misleading.
• Known matching and nonmatching examples were tested.
• Duplicate keys, leading zeros, and imported text were reviewed.
• The result agrees with the source record.

Frequently asked questions

Does XLOOKUP use exact matching automatically?

Yes. Exact matching is the default. Adding 0 as the match mode is optional, but it can make the intended behavior more obvious to someone reviewing the formula.

Can XLOOKUP search to the left?

Yes. The return array can be to the left or right of the lookup array because XLOOKUP does not depend on a column index number.

What happens when the lookup value is duplicated?

The normal search returns the first match. You can change the search mode to search from the last item, but duplicate business keys should usually be reviewed and corrected.

Does XLOOKUP work in every Excel version?

No. It is available in supported modern Excel versions, including Microsoft 365, but older perpetual versions may not include it. Use a compatible INDEX-and-MATCH formula when the workbook must support those versions.