XLOOKUP and VLOOKUP are two of the most popular Lookup and Reference functions in Excel. When our clients schedule advanced Microsoft Excel training, these functions always generate a lot of interest.
While both functions are incredibly useful, they each come with different capabilities and limitations you should be aware of.
Whare Are Lookup Functions in Excel?
Excel has an entire collection of functions in the Lookup & Reference category, including ADDRESS, LOOKUP, HLOOKUP, VLOOKUP, and XLOOKUP.
So what do lookup functions do exactly?
Lookup functions let you find a value in a data list or table then return a related value from the same row (or column).
An example might be helpful.
How to Find a Lookup Value
Think about the departures board at an airport. Here is a photo from the airport in Los Angeles (LAX).
If someone asked you what time American 6183 is scheduled to leave, how would you find out?
For a lot of people, this is a pretty familiar scenario.
You would scan down the left column until you find American 6183… then you would locate the departure time from another column in the same row. American Airlines flight 6183 is scheduled to leave at 3:55PM.
This is exactly what the XLOOKUP and VLOOKUP functions do!
How to Use the VLOOKUP Function in Excel
The VLOOKUP function in Excel is used to locate a return value in a range or table with a vertical orientation. In other words, the data is listed in rows.
The VLOOKUP function has the following syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
lookup_value is what you’re looking up.
table_array is range where the lookup value is located.
col_index_num specifies the column that contains the return value.
range_lookup is an optional parameter. True = approximate match, False = exact match. If omitted, range_lookup defaults to True.
Using VLOOKUP to Find an Exact Match
Let’s take a look at Excel’s VLOOKUP function in action. In the following example, we’ll find out which gates various flights will depart from.
In cell B4, I typed =VLOOKUP(A4,$D$1:$H$21,3,FALSE) and pressed Enter.
Here’s the rundown on all of the parameters used in the VLOOKUP function:
- Cell A4 contains the value I want to look up (China 5).
- $D$1:$H$21 is the range containing my lookup table.
- The return value is in column 3.
- I want an exact match so I set range_lookup to False.
Important Details About VLOOKUP
VLOOKUP always parses the first column in table_array for your lookup value.
Table_array would need to be sorted by column 1, if we were looking for an approximate match (range_lookup = True).
It’s best to use absolute cell references when specifying table_array, if you want to copy the VLOOKUP function to additional cells.
How to Use the XLOOKUP Function in Excel
Microsoft introduced XLOOKUP in Office 365 and Excel 2021. It provides more flexibility then VLOOKUP but it isn’t available in older versions of Excel.
The XLOOKUP function is used to locate a return value in a range or table with a vertical or horizontal orientation. In other words, XLOOKUP can be used to find data in rows or columns!
The XLOOKUP function has the following syntax:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
lookup_value is what you’re looking up.
lookup_array is the range or array to search.
return_array is the range or array containing values you want to return.
if_not_found is an optional parameter that specifies text to display if no match is found.
match_mode is an optional parameter that specifies match type. 0 = exact match or #N/A if not found (default), -1 = exact match or next smaller item, 1 = exact match or next larger item, 2 = wildcard match with *, ?, or ~.
search_mode is an optional parameter that specifies how to search for results. 1 = start with first item (default), -1 = start with last item, 2 = binary search where lookup_array is sorted in ascending order, -2 = binary search where lookup_array is sorted in descending order.
Using XLOOKUP to Find an Exact Match
Let’s illustrate how Excel’s XLOOKUP function works by looking at a couple of examples. We’ll start by finding out which gates various flights will depart from.
In cell B4, I typed =XLOOKUP(A4,$D$1:$D$21,$F$1:$F$21) and pressed Enter.
Let’s go over each of the parameters in the XLOOKUP function:
- Cell A4 contains the value I want to look up (China 5).
- $D$1:$D$21 is the lookup array.
- $F$1:$F$21 is the return array.
Important Details About XLOOKUP
XLOOKUP parses the range you specify in lookup_array for your lookup value. The lookup array can be a row or column.
After finding the specified lookup value, XLOOKUP returns related data from return_array. return_array needs to be the same size as lookup_array, otherwise a #VALUE error will be displayed.
Absolute cell references should be used when specifying lookup_array and return_array, if you intend to copy the XLOOKUP function to adjacent cells.
XLOOKUP defaults to an exact match.
Useful XLOOKUP Features
The XLOOKUP provides more flexibility than VLOOKUP. Let’s look at a couple of features that make XLOOKUP so powerful.
In the following example, I used XLOOKUP to find out which flight is assigned to various gates at the airport.
In cell B4, I typed =XLOOKUP(A4,$G$2:$G$21,$E$2:$E$21,”Unassigned”) and pressed Enter.
Let’s go over each of the parameters in the XLOOKUP function:
- Cell A4 contains the value I want to look up (101).
- G$2:$G$21 is the lookup array, $E$2:$E$21 is the return array. The return array is positioned to the left of the lookup array. This wouldn’t be possible using VLOOKUP.
- I added the text “Unassigned” as the if_not_found argument so, rather than displaying an error, XLOOKUP up displays Unassigned.
Key Differences: XLOOKUP and VLOOKUP
When you’re trying to decide which function is best in regards to XLOOKUP vs VLOOKUP, there are a few things you should keep in mind.
XLOOKUP is only available in the latest versions of Excel. Microsoft introduced the function in Office 365 and Excel 2021 so not everyone is going to have XLOOKUP. VLOOKUP, on the other hand, is available in every version of Excel.
XLOOKUP is more powerful and flexible than VLOOKUP. XLOOKUP works data in rows or columns and you have more control over how lookup data is parsed.
The lookup array doesn’t have to be in the leftmost column of your lookup table and you can add or remove columns without having to redefine your return array in XLOOKUP.
In the end, it comes down to compatibility. If your organization has standardized on Office 365 or Excel 2021, you should use XLOOKUP wherever you can. If your colleagues and business partners are still using older versions of Excel, you better stick to VLOOKUP!
