Excel's XLOOKUP function searches one column for a value and returns the matching entry from the same row in another column. This guide shows you how to write that basic formula and how to check whether your copy of Excel has the function at all.
Microsoft's XLOOKUP help page describes it this way: it looks in one column for a search term and returns a result from the same row in another column, whichever side that column is on. That last part is the main reason to use it. The column you want back can sit to the left or the right of the column you search.
Check your Excel version before you start
According to Microsoft's XLOOKUP page, XLOOKUP is not available in Excel 2016 or Excel 2019. Those versions can still open a workbook that someone else created with XLOOKUP in it.
If you type the formula in one of those versions, it won't work as a new formula. That is the most common reason for an XLOOKUP problem that has nothing to do with the formula itself. If XLOOKUP is missing, the fix is a newer version of Excel, not a rewritten formula.
Write a basic XLOOKUP formula
Here is a simple layout to illustrate the pattern:
=XLOOKUP(B2,B5:B14,C5:C14)
In that layout, B2 holds the value you're looking for, B5:B14 is the column Excel searches, and C5:C14 is the column it returns a result from, on the same row as the match.
- Put the value you want to look up in a cell of its own, such as B2.
- Click the cell where you want the answer.
- Type the formula, changing the three references to match your sheet.
- Press Enter.
The cell then shows the entry from the return column that sits on the same row as the match. For example, if the value in B2 is found in B9, the formula shows whatever is in C9.
Keep the search column and the return column the same height, as they are in the layout above.
When the formula doesn't find your value
The formula can only return something if the value in B2 actually appears in the search column. If the result isn't what you expect, compare the text in B2 with the entries in B5:B14 character by character: an extra space or a different spelling means there is no matching row to return from. Also check that your references point at the right columns, so Excel is searching the column that holds your lookup values and returning from the column you want.
