Microsoft Excel XLOOKUP vs VLOOKUP
In this article we will be looking at the XLOOKUP function in Excel and comparing its long-standing predecessor VLOOKUP to see what advantages XLOOKUP has and why you would use it instead. So, we are going to create an Excel lookup first using VLOOKUP and then we will be performing the same task with XLOOKUP. Alternatively watch our video about it.
Download XLOOKUP example spreadsheet referenced in this article and as used in the video.
We have identified 7 improvements and advantages of XLOOKUP v VLOOKUP
At this point, a quick note that XLOOKUP has been in Excel 365 since 2019, but it didn’t go into standalone Excel until Excel 2021. This means that if you are creating a file to share with anyone using an earlier version of Excel, you should not use XLOOKUP.
The example dataset we will be using includes a list of contacts that we want to be able to search via a name to view various other pieces of contact information, as well as a sizing chart where we want to find which size matches a body measurement.
First we want to turn our list of contacts into an Excel table, which will allow us to manage the data better and make it more like an Excel CRM system. Go to the Contacts sheet of this spreadsheet to see the data we have saved here. Select the range of cells that contain all the contact data including the column headings On the ribbon at the top of the screen, click on Insert, then click the Table button under Tables.
Tick the “My table has headers” tick box and then click OK.
Next we can give the table a useful name instead of the default Table1, to make it easier to reference later. Having just created the table we should now be on the Table Design tab on the ribbon at the top of the screen. We can see the table name in the top left. We will call it ContactsTable, so click and enter the new name.
Now we have our table of data we can create a look up formula to return information such as an email address for a contact’s name. The traditional way of doing this would be to use a VLOOKUP formula. With Excel VLOOKUP you set up the range you want to search in and return a value from, just like we have here, however the first column of the range used for the VLOOKUP function has to be the one you are searching in. So, you can only return information from somewhere to the right of the lookup column and the lookup area has to start with that column.
Here it is in practice. The first thing we need to do, is look at our data table and count which column the email address is in, starting with the Full Name column as 1. In our case, the email address is therefore column 5. We need to remember this number! Or better still, we can enter the column numbers in cells above the headings like we have here. Not only does this spare us from repeatedly having to count and then remember the numbers, but we will also show you how this is handy for when we want to use VLOOKUP formulas to return multiple pieces of information.
We first enter the value we want to search for (in quotes) or we point to the cell that will contain the name we want to search for. In this case we select a cell that we will be typing a contact name into. We use $ marks around the cell name reference so if we copy the formula it will continue to use this input cell to find the name we are looking up. We can use the F4 key to do this. Next we select the range we want to search, remembering that the first column must be the name column and then we must stretch out to the right as far as we need to be able to return data from.
Now we type the number of the column within that range that we want to return the email from – 5 if you remembered – or we can point to where the column number is stored in a cell, which we are doing here. And then we specify false to make sure we only get a return for an exact match. We will talk more about this exact match feature later on in this video. Even in VLOOKUP it is a useful feature. If we type John Dance in the input cell you will see that it will find the matching email address for them.
XLOOKUP is an improvement on VLOOKUP by adding new functionality and streamlining formulas. We have hinted at the limitations of VLOOKUP in comparison, but here is a demonstration of a list of the improvements made to look ups by XLOOKUP. They become more and more powerful as we go down though.
1. No Need for Column Numbers
For starters we can choose the search column and return column separately, so you no longer have to select a range that contains both and that has the search column at the start. This also means we don’t need to use column numbers. Furthermore, we can directly select our return column and since our contacts are in a table we can use the table’s column names. We can select the columns by selecting the data in the column, by clicking just above the column header, or type in the names.
With named table columns, we can’t use $ marks around the input cell to ensure it stays the input cell. Instead we need to put an open bracket before the name of the column, Full Name, then add a colon and add the Full Name column again, followed by a close bracket. This will ensure the Full Name column is what is used even when we apply this formula to the other cells we want to use Xlookup in.
2. Selecting a Prior Column
VLOOKUP is limited to only returning columns to the right of the one you search. XLOOKUP can return any of the columns in the table including columns to the left of the search column Full Name, such as the Date Added.
3. Return Multiple Columns With One Formula
XLOOKUP can also be used to return multiple columns with one formula. For this, we will select the range of columns we want to return, or return the whole table entry. The formula is only in the first cell but it will populate all the following cells in the row with the information from the selected range without needing to copy across the formula.
This first example returns the whole table entry, so has the return range as ContactsTable
This second example does the same thing but here instead of returning the whole row it only returns a group of columns from Email through to Next Steps.
Very important note though - if there are already entries in the cells that XLOOKUP is going to populate, you get a #SPILL! error message in the cell and nothing is shown in the other columns.
4. Choose what to Display for No Match
VLOOKUP returns #N/A when it cannot find a match. With XLOOKUP you can enter text for it show. In this example, we have chosen to display “Not found”, but we could instead specify “” to display nothing.
You can combine VLOOKUP with other functions sucah as IFERROR to make it show something different if there is no match but with XLOOKUP you don’t need to.
5. Using Wild Cards
Both VLOOKUP and XLOOKUP can use wildcards in their search text. For example with VLOOKUP if we type *tay* the formulas return the values for the first person it finds in the table with “tay” within their name. Note that if we change the sort order of the table, it can change the results. But we can’t change it away from returning the first matching entry in this case and we can’t stop it from using wildcards.
Xlookup doesn’t use wildcards by default but we can turn them on by using its Match_mode feature. For wildcard matching we enter 2 here. See that if we don’t put 2 in the formula then it will look for an exact match for our search text, and will therefore not find one in this case.
6. Range Lookups
This options allows us to look up return values for a range of inputs. As a simplistic example, suppose you want to return a size choice for a t-shirt dependent on someone’s chest measurement, you would set up the table so that the smallest measurement applicable to each size is in the first column, and then the applicable size is in the 2nd column. We have also added a 3rd column to explain how VLOOKUP applies the rules.
The VLOOKUP formula lookups up the measurement we enter and returns the size from column 2. We haven’t specified to use an exact match, so it finds the one previous.
Note if we reverse the sort order of the table, it breaks the formula. Worse still, if we sort the table in a random order for the first column (e.g. by sorting on the size column instead), the VLOOKUP returns the wrong result. The first column has to be in ascending order for the feature to work in VLOOKUP.
Now let’s look at the equivalent formula using XLOOKUP. To replicate the behaviour of VLOOKUP we use -1 (with a dash or minus before the 1) as the setting for its Match Mode.
Note that the XLOOKUP works correctly still when we reverse the sort order or when we use a random order. Much better!
Furthermore, we can change the setting of Match Mode to 1 so that it returns the next larger item instead of the next smaller one. In our case that’s probably a better choice as its more intuitive to specify the maximum chest size that each t-shirt size will accommodate .
7. Search Mode
Finally there’s the Search Mode feature, which doesn’t exist at all in VLOOKUP. In its basic form, this allows us to find the last entry rather than the first. Note that this does depend on the order of the table.
Another feature of the Search Mode parameter is to invoke binary searching. This matters for large sets of data and improves speed enormously in those cases.
XLOOKUP can go horizontally too - equivalent to the HLOOKUP function. HLOOKUP was a function designed for tables set out the other way round where you want to look across the columns for a piece of information and then return a value from a particular row. The Prices sheet in the example file shows the kind of data we mean. We will explore using this further to perform 2-way lookups in a future video. Subscribe to our YouTube channel now so that you can look forward to the next video.
If you would like to keep informed about new videos and articles please enter your details below. We only send emails 1-2 times a year and never pass details to anyone else.
