Join the Excel conversation on Slack
Ask a question or join the conversation for all things Excel on our Slack channel.
XLOOKUP improves on VLOOKUP by allowing lookups both to the left and right, offering more flexibility, reducing formula errors, and including advanced features like custom error handling and exact match as the default. For a more detailed XLOOKUP vs VLOOKUP comparison read on.
What are the XLOOKUP & VLOOKUP functions in Excel?
The XLOOKUP and VLOOKUP functions in Excel are used to find, or 'lookup', a value from a table or a list and then return a related result. In this resource, we’ll discuss XLOOKUP vs VLOOKUP with examples of how to use each.
In the image below you have a dataset on the left where members signed up for workshops, called the Workshop Registrations dataset. This dataset is missing some information about the members, for example, the “Last Name” field.
The information about the members can be found in another dataset, called the Members dataset, and can be retrieved from there to complete the required fields in the Workshop Registrations dataset.
To get the information from one dataset to complete the other, you would normally use a VLOOKUP. If you have access to Microsoft 365, you could consider the new XLOOKUP to get the job done instead.
How you look up the member’s detail in the dataset is a little different for XLOOKUP and VLOOKUP. Let's take a look at the syntax and differences of each.
Download your free practice file
Use this free Excel file to practice along with the tutorial.
Syntax
Both formulae require a minimum of three arguments. The square brackets indicate the optional arguments.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
VLOOKUP vs XLOOKUP: Comparison table
| VLOOKUP | XLOOKUP | |
|---|---|---|
| Exact match default | N | Y |
| Return value to the right of lookup | Y | Y |
| Return value to the left of lookup | N | Y |
| Lookup in row | N | Y |
| Return more than one value | N | Y |
| Lookup column needs to be sorted | Approximate match only | Binary search only |
| Search from top to bottom | Y | Y |
| Search from bottom to top | N | Y |
| Binary search | N | Y |
| Custom message if lookup value not found | N | Y |
| Wildcard search | Y | Y |
| Exact search | Y | Y |
| Approximate search returning next smaller value | Y | Y |
| Approximate search returning next larger value | N | Y |
| May return an incorrect value if lookup values are not sorted? | Y - approximate |
N - approximate, Y - Binary |
| Add new columns to search table may break the formula | Y | N |
Lookup value
The value you want to find on the new list (e.g. the member ID). Both VLOOKUP and XLOOKUP usually lookup (search for) a single value.
VLOOKUP vs. XLOOKUP argument comparison:
Table array vs. Lookup array
This represents the biggest difference between VLOOKUP and XLOOKUP and in the example above will be the member’s dataset where you would find both the member (lookup value) and the member’s first name or last name (returned value).
The VLOOKUP formula uses table_array, this area indicates the column for the lookup value and stretches to include the column for the returned value. The next argument (return array) refers back to this array by indicating the relevant column number of the table array that houses the return value.
Excel is programmed to search for the lookup value in the leftmost column of this range and return a value to the right of this, indicated by the column number from the next argument.
The XLOOKUP separates the lookup array and the return array into two arguments. The lookup array should house the value Excel will search for and the return array argument will house the value to be returned.
The separation of the arrays makes the XLOOKUP much more flexible than the VLOOKUP. The lookup array can now be to the left or the right of the return value column and the XLOOKUP can act as a VLOOKUP or an HLOOKUP.
VLOOKUP vs. XLOOKUP argument comparison:
Column index number vs. Return array
This argument indicates the range where the returned value can be found. In our example, this would be the first name of the member.
In a VLOOKUP this is a single number that refers back to the table array (the previous argument that indicated the entire range where the search value and returned value can be found).
The VLOOKUP is easy to ‘break’ if this column number is hardcoded into the argument, which is an important benefit to the XLOOKUP.
The XLOOKUP requires an entire range of values for this argument to indicate the array where the return value may be found. This range has to be aligned with the lookup array (previous argument) in length and position to avoid a #VALUE! error.
The return array in the XLOOKUP can also accommodate more than one return column in the return array, which will return more than one value related to the lookup value. You will have to use two VLOOKUPs to manage the same.
The past two arguments (the separation of the ranges) indicate the biggest benefits to the XLOOKUP:
- The lookup array can be to the right or left of the return array
- The XLOOKUP is harder to break because the two (lookup and return) arrays are relative and not hardcoded into the formula.
This is where users rave about the XLOOKUP and are ready to dispose of the VLOOKUP immediately. BUT, if you don’t hard code the column number in the VLOOKUP, but manage to make this argument the result of a formula, the VLOOKUP will provide a benefit that may be hard to replicate with an XLOOKUP alone.
Optional arguments
If not found (XLOOKUP only)
If the XLOOKUP cannot find a value, it returns a default or a customized message. The message may read, “The member you are looking for is not registered in our database.”
Unless you add an IFERROR, the VLOOKUP will return its usual #N/A! (not available) error, which may make you believe you did something wrong and the VLOOKUP is not working.
VLOOKUP vs. XLOOKUP argument comparison:
Range lookup vs. Match mode
Exact (FALSE) and approximate (TRUE) matches in VLOOKUP have similar possibilities in XLOOKUP. Compared, it would look like this:
|
XLOOKUP programmed options |
VLOOKUP options that perform similarly |
|---|---|
| Exact (0) - Default | Exact (1) |
| Approximate (exact or next smaller value) (-1) | Approximate – Default |
| Approximate (exact or next larger value) (1) | None |
| Wildcard (?*~) (2) | Exact (1) match using the same wildcard characters |
XLOOKUP defaults to an exact match whereas the range lookup argument in VLOOKUP defaults to an approximate match. Since an exact match is used most often, this setting would make the XLOOKUP more effective. On top of this, the XLOOKUP offers an additional option of an approximate match returning the next larger value.
Search mode (XLOOKUP only)
The VLOOKUP is programmed to search from top to bottom (first to last) and will return the first matching item for exact and approximate matches.
The XLOOKUP has four possible search options:
- First to last (1) - default
- Last to first (-1)
- Binary search - assume ascending order sort for lookup range (2)
- Binary search - assume descending order sort for lookup range (-2)
The methods of searching open up additional functionality for the XLOOKUP - you can search for the last occurrence of an item in a range. The binary search method is faster in sheets with large quantities of data but relies on the fact that the data in the lookup range is sorted.
The VLOOKUP searches from first to last and can only look from last to first with the use of some serious maneuvering in Excel.
Other differences
Sorting of lookup range
The VLOOKUP approximate (TRUE) search will return incorrect values if the lookup range is not sorted in ascending order.
The XLOOKUP is inhibited by sorting when it searches from last to first or when it uses binary search.
Compatibility with older versions of Excel
VLOOKUP has no compatibility issues with older versions of Excel. XLOOKUP is only available in Microsoft 365 and the online version - Excel for the Web.
If you want to share a worksheet with XLOOKUPs with collaborators, make sure they also have access to Microsoft 365 for the time being.
Horizontal lookup
The XLOOKUP not only improves the VLOOKUP functionality but also replaces HLOOKUP.
Troubleshooting
Inclusion of headings in the lookup range
It is always risky to include headings from the lookup range in the VLOOKUP or the XLOOKUP as Excel may interpret the heading as part of the data and may return incorrect values that seem correct as a result.
Error messages
VLOOKUP error:
#N/A - value searched for is not available.
- Example: the member number cannot be found in the member dataset.
#REF - the value to return is outside of the defined table array.
- Example: you selected five columns and want a return value from column 6.
XLOOKUP error:
#N/A - value searched for is not available.
- Example: the member number cannot be found in the member dataset. (Same as VLOOKUP.)
#REF - the other workbook as referred to in the formula is not open.
#VALUE - the dimensions of the lookup array and return array are not aligned in position and length.
- Example: the lookup array is five rows long and the return array is four rows long, or the lookup array is in a row and the return array is in a column.
When to use XLOOKUP or VLOOKUP
XLOOKUP can be used for the same tasks as VLOOKUP and then has a few extra uses. It may be better to show you examples.
Example 1 - Find a value that exists and is unique
The first example looks for a member number that exists (exact match) in the members dataset, and is unique - a value that you would expect to find easily.
In cell B4, we enter:
=VLOOKUP(A4,$J$4:$L$14,2,FALSE)
And in cell B4:
=XLOOKUP(A4,$J$4:$J$14,$K$4:$L$14,"Membership number not found")
Also note that the XLOOKUP used one formula to return two values.
Example 2 - Find a value that does not exist
What happens when Excel is asked to find a value that does not exist in the dataset? In the example below, the Member ID 11111 does not exist.
VLOOKUP
The exact VLOOKUP returns a #N/A - which translates to not available.
XLOOKUP
XLOOKUP exact match only finds nothing, as expected, and returns a customized message.
Example 3 - Find a value to the left
In this example, Excel has to look for the value 4 in the column named ‘99’ and return the associated ‘number’ to the left.
VLOOKUP cannot comply, so an alternative index and match can be used.
The XLOOKUP is fairly simple in comparison.
In cell D4, we enter:
=XLOOKUP(A4,$J$4:$J$14,$I$4:$I$14)
Example 4 - Find the last available occurrence of a value
This example shows a list of customer orders. If we wanted to know what the last order total was for a certain customer, we could use the new XLOOKUP last to first search functionality. In this example, the database needs to be sorted by date.
Conclusion
So there you have it, a breakdown of XLOOKUP vs VLOOKUP.
VLOOKUP has several limitations which are addressed with the introduction of XLOOKUP. The XLOOKUP function is a definite improvement to VLOOKUP and replaces a lot of the bypasses that you had to rely on to get a VLOOKUP to work in certain instances (INDEX, MATCH, IFERROR, and other maneuverings).
XLOOKUP is unfortunately not available for older versions of Excel, and you will have to keep this in mind when sharing worksheets.
VLOOKUP was a trusted old companion and it may still have a place in certain instances - so don’t write it off yet. But the XLOOKUP is, without a doubt, a great and much-needed improvement.
Take the next step!
To learn more about XLOOKUP, VLOOKUP, and other essential functions in Excel, try our Excel - Basic and Advanced course now.
Ready to become a certified Excel ninja?
Start learning for free with GoSkills courses
Start free trial
Join the Excel conversation on Slack
Ask a question or join the conversation for all things Excel on our Slack channel.
