VLOOKUP finds a value in the first column of a table and returns the value from a column a set number of places to the right, while XLOOKUP is an improved version where you specify the lookup range and the return range separately. The noticeable differences are that XLOOKUP looks for an exact match by default and works even when the column you want to return is on the left. However, XLOOKUP isn't available in Excel 2016 and Excel 2019, so you also need to check the Excel versions of the people you share files with.
"I need to attach media names using campaign codes. Should I use VLOOKUP or XLOOKUP?" When you're cleaning up ad reports or order lists, you often need to pull values from another sheet based on codes in one sheet. For a long time this was VLOOKUP's job, but Microsoft now recommends trying XLOOKUP first, right at the top of the VLOOKUP help article. So does that mean you no longer need VLOOKUP? In this post, I'll go over how the two functions differ and when to use which.
1. How does VLOOKUP find values?
The VLOOKUP syntax is =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).
The help article breaks this structure down as "the value you want to look up, the range where you want to look it up, the column number that contains the return value, and approximate or exact match (1/TRUE or 0/FALSE)."
- lookup_value: The value you want to find. It must be in the first column of the range you specify in
table_array. - table_array: The range that contains both the lookup value and the return value.
- col_index_num: The column number of the return value, counting the leftmost column of the range as 1.
- range_lookup: TRUE finds an approximate match, FALSE finds an exact match.
VLOOKUP works a lot like reading a theater seating chart. You find the row you want by its name on the far left, then count how many seats to the right.
That's why the help article says the secret to VLOOKUP is organizing your data so that the value you look up is to the left of the return value.
For example, if column A has campaign codes and column C has media names, you'd write =VLOOKUP(F2, A2:C100, 3, FALSE), where 3 means the third column counting from column A.
Because you type the column number directly, it's a good idea to double-check that the number is still right after inserting or deleting columns in the table.
2. What's the most confusing part of using VLOOKUP?
It's that leaving the fourth argument blank makes it search for an approximate match.
According to the help article, if you don't specify range_lookup, the default is TRUE, meaning approximate match. In that case, it assumes the first column is sorted numerically or alphabetically and finds the closest value.
If the first column isn't sorted, you may get an unexpected value back, so when you're looking for an exact value like a code or name, always put FALSE at the end.
The help article's "Common problems" table also lists these errors.
| Error | Cause (official help) |
|---|---|
| #N/A | When searching with FALSE and there's no exact match, or when searching with TRUE and the lookup value is smaller than the smallest value in the first column |
| #REF! | When col_index_num is greater than the number of columns in the range |
| #NAME? | Usually when quotes are missing around text in the formula |
Another thing to know is that VLOOKUP only searches from left to right. The VLOOKUP, INDEX, and MATCH help article advises using INDEX and MATCH together instead of VLOOKUP if the lookup value isn't in the leftmost column. So how did XLOOKUP solve this problem?
3. How is XLOOKUP different from VLOOKUP?
The syntax in the XLOOKUP help article is =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]).
The key point the article makes is that XLOOKUP takes a lookup array and a return array separately, whereas VLOOKUP uses a single table range and a column number.
Rewriting the earlier example with XLOOKUP gives =XLOOKUP(F2, A2:A100, C2:C100), and you no longer need to count column numbers.
| Comparison | VLOOKUP | XLOOKUP |
|---|---|---|
| Default match type | Approximate (TRUE) | Exact match (0) |
| Search direction | Only rightward from the first column | Works whichever side the return column is on |
| How you specify the return | Column number | Return range |
| When not found | #N/A | The value in if_not_found; #N/A if left blank |
| Return multiple columns at once | Not supported | Possible (e.g., name and department in one formula) |
| Search order | No argument | From the first item (default) or in reverse from the last item |
The optional arguments are a bit richer, too.
- if_not_found: The value to show when there's no match. For example, if you enter
"Not registered", that text appears instead of #N/A. - match_mode: 0 is exact match (default), -1 returns the next smaller item if there's no match, 1 returns the next larger item if there's no match, and 2 is a wildcard match using
*,?, and~. - search_mode: 1 searches from the first item (default), and -1 searches in reverse from the last item. 2 and -2 are binary searches that assume a sorted range; the help article notes that you'll get wrong results if it isn't sorted.
The help article's examples also show nesting XLOOKUP to look up vertically and horizontally at the same time, which it describes as similar to using INDEX and MATCH together. It also says XLOOKUP can replace HLOOKUP, which searches horizontally.
Note: Are there Excel versions that can't use XLOOKUP?
Yes. The XLOOKUP help article explicitly states that XLOOKUP is not available in Excel 2016 and Excel 2019. The Excel functions (alphabetical) list also marks XLOOKUP with a "2021" version tag, which it explains means the function was introduced in that version and isn't available in earlier versions. The help article's "Applies to" list also includes Excel 2016 and 2019, which can be confusing, but the guidance in the body is that it can't be used in those two versions.
The help article also notes that you may end up using, in Excel 2016 or 2019, a workbook containing XLOOKUP formulas that someone created in a newer version. So when you send a file with XLOOKUP to a colleague, it's a good idea to check their version first. VLOOKUP, on the other hand, lists everything from Excel 2016 to Microsoft 365 in its "Applies to" section. If you don't know which Excel version the recipient uses, I think that's the most practical reason to still use VLOOKUP.
4. So when should you use which?
Based on the official help articles, here are my criteria.
- When to consider XLOOKUP first: When both you and your colleagues use Microsoft 365 or Excel 2021 or later, when you need to return a column to the left of the lookup value, when you want to return multiple columns at once, or when you want to leave a label like "Not registered" for values that aren't found
- When to stick with VLOOKUP: When you exchange files with Excel 2016 or 2019 users, or when reports you've already built with VLOOKUP are working fine
- If you use VLOOKUP: Put
FALSEin the fourth argument and arrange the table so the lookup value is in the first column of the range. If you need to return a column on the left, use the INDEX and MATCH combination.
To sum up, the two functions do the same job, but Microsoft introduces XLOOKUP as an improved version of VLOOKUP that searches in any direction and returns exact matches by default. Not having to worry about the approximate-match default, column numbers, or the left-column limitation is probably the difference you'll feel in practice. If you want to summarize the looked-up results by several criteria, I also recommend using it together with a PivotTable. I'm going to practice using the two functions for the right situations as I clean up my own reports, so for detailed argument descriptions, please refer to the official help articles.
FAQ
- What's the difference between VLOOKUP and XLOOKUP in one line?
- VLOOKUP searches the first column of a range, returns a value to the right by column number, and defaults to approximate match. XLOOKUP lets you specify the lookup range and return range separately so it can search in any direction, and it defaults to exact match. You can also set the value to show when nothing is found as an argument.
- Can I use XLOOKUP in Excel 2016 or 2019?
- Microsoft's XLOOKUP help article says XLOOKUP is not available in Excel 2016 and Excel 2019. The Excel functions list also marks XLOOKUP as a function introduced in Excel 2021. If you exchange files with people using these versions, VLOOKUP or the INDEX and MATCH combination is the safer choice.
- Why does VLOOKUP return the wrong value?
- If you leave the fourth argument (range_lookup) blank, it works as an approximate match (TRUE), and if the first column isn't sorted, it may return an unexpected value. To find an exact value, put FALSE in the fourth argument.
- How many basic arguments does XLOOKUP have?
- There are three required arguments: the value to find (lookup_value), the range to search (lookup_array), and the range to return (return_array). You can optionally add a value for when nothing is found (if_not_found), the match type (match_mode), and the search order (search_mode).
Related posts
Posts worth reading alongside this one when cleaning up data in Excel.
- What Is an Excel PivotTable, and When Should You Use It? - How to summarize looked-up data from several angles
- How to Fix Broken Korean Text in Excel Data - Fixing garbled Korean characters when opening downloaded data in Excel