You have a list of product codes on one sheet and the prices on another, and you need to bring them together. Copying each price by hand is slow and easy to mess up. This is where VLOOKUP and XLOOKUP come in. This guide explains how to use VLOOKUP and XLOOKUP in Excel with simple examples, so you can pick the right one and stop fighting #N/A errors.
VLOOKUP vs XLOOKUP: which one should you use?
Both functions do the same basic job: look for a value in one place and return a matching value from somewhere else. The difference is age and flexibility.
- VLOOKUP works in every version of Excel, including very old ones, and in Google Sheets. It can only look to the right, and it breaks easily if someone inserts a column.
- XLOOKUP is newer. It’s available in Excel for Microsoft 365, Excel 2021 and later, and Excel for the web. It can look left or right, defaults to an exact match and lets you set a custom message when nothing is found.
If everyone who opens your file has a recent version of Excel, use XLOOKUP. If you share files with people on Excel 2016 or older, stick with VLOOKUP, because XLOOKUP will show a #NAME? error on their computers.
How VLOOKUP works
The formula looks like this:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- lookup_value: what you’re searching for, such as a product code in cell A2.
- table_array: the range that holds your data. The value you’re searching for must be in the first column of this range.
- col_index_num: which column of that range to return, counted from the left (1, 2, 3…).
- range_lookup: type FALSE for an exact match. Almost always use FALSE.
A simple VLOOKUP example
Say Sheet2 has product codes in column A, names in column B and prices in column C. On Sheet1, the codes are in column A and you want the price in column B.
- Click cell B2 on Sheet1.
- Type
=VLOOKUP(A2, Sheet2!$A$2:$C$500, 3, FALSE) - Press Enter.
- Double-click the small square at the bottom-right corner of B2 to copy the formula down.
The dollar signs lock the range so it doesn’t shift as you copy the formula down. Forgetting them is one of the most common reasons VLOOKUP returns wrong results halfway down a list.
Why you should almost always type FALSE
If you leave the last argument out, VLOOKUP uses an approximate match. With unsorted data, that can return a price from the wrong product without any error. It looks fine, and that’s the dangerous part. Use FALSE unless you’re doing something like tax brackets or grading scales, where approximate matching is the goal.
How XLOOKUP works
XLOOKUP splits the search column and the result column into separate arguments, which makes it easier to read:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
For most tasks, you only need the first three or four parts.
The same example with XLOOKUP
=XLOOKUP(A2, Sheet2!$A$2:$A$500, Sheet2!$C$2:$C$500, "Not found")
Read it out loud and it makes sense: look for A2 in the code column and return the matching value from the price column. If the code doesn’t exist, show “Not found” instead of an error.
Things XLOOKUP does that VLOOKUP can’t
- Look to the left. If your codes are in column D and names in column A, XLOOKUP doesn’t care. VLOOKUP would need you to rearrange the columns.
- Survive new columns. Insert a column in the middle of your data and XLOOKUP keeps working, because it points to the actual result column instead of a number like 3.
- Find the last match. Set the last argument (search_mode) to
-1to search from the bottom up. That’s useful for finding the most recent order from a customer. - Return several columns at once. Make the return_array two or three columns wide and the results spill across neighboring cells.
Fixing the most common lookup errors
#N/A
Excel couldn’t find the value. The usual suspects:
- Extra spaces. “AB100 ” with a trailing space doesn’t match “AB100”. Clean the data with
=TRIM(A2). - Numbers stored as text. A code that looks like 1050 might be text on one sheet and a number on the other. If you see a small green triangle in the cell corner, click it and choose Convert to Number.
- The value really isn’t there. Check with Ctrl + F on the source sheet.
To show something friendlier than #N/A with VLOOKUP, wrap it in IFERROR: =IFERROR(VLOOKUP(A2, Sheet2!$A$2:$C$500, 3, FALSE), "Not found"). With XLOOKUP, just use the fourth argument.
#REF!
With VLOOKUP, this means your column number is bigger than the range. If your range covers columns A to C, you can’t ask for column 4.
#NAME?
Either there’s a typo in the function name, or the version of Excel opening the file doesn’t support XLOOKUP. Switch to VLOOKUP or INDEX and MATCH if you need to share with older versions.
Wrong results with no error
Check for a missing FALSE in VLOOKUP and for ranges without dollar signs that slid down as you copied the formula.
What about INDEX and MATCH?
Before XLOOKUP existed, power users combined INDEX and MATCH to look left and avoid column numbers: =INDEX(Sheet2!$C$2:$C$500, MATCH(A2, Sheet2!$A$2:$A$500, 0)). It still works in every version, so it’s a good fallback when you need flexibility and can’t count on XLOOKUP being available.
Lookups pair nicely with summaries. Once your data is merged, try our guide on how to create a pivot table in Excel to total it up by category or month. If you’re putting together invoices from a spreadsheet, you may also like our roundup of invoicing software for small businesses.
FAQ
Is XLOOKUP available in Excel 2016 or 2019?
No. XLOOKUP is available in Excel for Microsoft 365, Excel 2021 and newer, and Excel for the web. In Excel 2016 and 2019 you’ll need VLOOKUP or INDEX and MATCH.
Can VLOOKUP look to the left?
Not on its own. VLOOKUP always searches the first column of the range and returns something to its right. Use XLOOKUP or INDEX and MATCH, or move the columns.
Does Google Sheets have XLOOKUP?
Yes, Google Sheets supports XLOOKUP with a very similar syntax, as well as VLOOKUP. Formulas you write in Excel usually work there with little or no change.
How do I look up a value from another workbook?
Open both files, start your formula, then click over to the other workbook and select the range. Excel adds the file name to the reference. Keep the source file in the same folder so the link doesn’t break.
