This article will go through examples of how to use XLookup vs VLookup to reference a by-row data table. We will highlight the advantages of using XLookup instead of Vlookup.
XLookup Syntax
XLookup([Lookup Value], [Search Array], [Return Array], [Default Result], [Match Mode], [Search Mode])
For a more complete description of the XLookup function, see the companion article: What is XLookup?
Examples Using XLookup
The following examples will be based on the data table on the left. It is a typical by-row data table of employee data. All of the information related to one employee appears in one row. The data table is sorted by the employee ID column.
In all of the examples, the cell(s) containing the formula and its result are highlighted.
Examples in this article
- Find a lookup value in a data table and return a specified value
- Find a lookup value in a data table and return multiple values
- The effect of Match Mode on XLookup
- Returning items to the left of the search column
Find a lookup value in a data table and return a specified value
XLookup
Here, side-by-side you can see the same query using XLookup and Vlookup. On the left, we are searching for the value in cell C13 (3) in the range C:4-C:7 (Employee ID column of our table) and looking for the result in the range D4:D7 (Last name). Results of the formula are highlighted.
Comparable VLookup
On the right, we are using VLookup to search for the value in cell C13. With VLookup, we specify a range that starts with the search range and continues through the results range. Then, we specify which column number (in this case 2) within the range to return.
One important difference is that if we insert a new column in the data table between columns C and D, XLookup will still work. The XLookup formula will automatically update the reference to the Return Array so long as:
- The data table is in the same workbook as the XLookup formula, or
- If the data table is in a different workbook, both workbooks are open when the data table is modified, or
- The Return Array is a named range in the workbook containing the data table
VLookup, however, will break. Our intended result will not be in the second column anymore, but would be in the third column.
Find a lookup value in a data table and return multiple values
XLookup
This example is the same as Example 1 above, except now we are returning multiple columns. We are searching for the value in cell C13 (3) in the range C4:C7 (Employee ID Column) and returning matching row from the range D4:F7 (Last Name, First Name and Department). Results of the formula are highlighted.
Comparable VLookup
We can accomplish the same thing with VLookup, but it requires a separate formula for each value. This is a simple example, but you can see how it could require many more formulas with VLookup than XLookup, adding to the spreadsheet’s complexity and to the time it takes to calculate.
The Effect of Match Mode on XLookup vs VLookup
In the following few examples, we will see what happens if we try to find a value that does not exist in our data table. In each case, the function will return “Not Found” if no match could be found, but you will see how March Mode can allow an approximate match.
XLookup
In examples 3-5, we are looking for employee ID 6 in the range C4:C7 (Employee ID) and want to return the matching value in the range D4:D7 (Last Name). Since employee ID 6 does not exist in our search range, XLookup checks to see if we specified a Match Mode. Example 3 specifies an exact match (Mode 0) and returns Not Found. Example 4 specifies Match Mode -1, and returns the next smaller item which in this case would be employee ID 5. Example 5 specifies Match Mode 1 and attempts to return the next larger item. Since there are no employee IDs larger than 6, it returns Not Found.
In example 6, we are searching for employee ID of zero, which does not exist. Specifying a Match Mode of 1 returns the next larger item, which in this case is employee ID 1.
Comparable VLookup
The VLookup function has an optional third parameter: Approximate Match. If Approximate Match is set to TRUE (the default) then VLookup will return the next smaller item, similar to a Match Mode of -1 in the XLookup example.
In this example, we are looking for the value in cell C23 (employee number 6) in the range C4:D7, and will return value in column 2 (Last Name). Since there is no employee number 6, if we tell VLookup to use an approximate match it will return the last name of employee 5, the next smaller value of employee ID.
For Approximate Match to work properly, the data table must be sorted in ascending order of the left column (column C in this example).
Returning values to the left of the search column
This type of lookup would not be possible with VLookup. In examples 7 through 14 we are looking for a last name in the range D4:D7 and returning the corresponding employee ID number found in the range C4:C7. We also vary Match Mode to see its effect on searching for items that do not exist in our data table.
Examples 7 through 12 are similar to examples 3 – 6 above except now we are searching for text instead of a number. In example 10 since Match Mode is set to 1, looking for Baker returns Beancounter because alphabetically BE is after Ba.
Examples 13 and 14 illustrate using wildcards in a search. In Example 13, we are looking for the first last name that begins with W. In Example 14, we don’t know the first letter of the last name, but somehow remember that it ends in yl.
Finally, example 15 illustrates that if we don’t include text to return when there is no match, the function returns a #N/A error.
Related Articles on XLookup
- Lookup Functions in Excel
- General discussion of lookup and reference functions available in Excel
- What is XLookup
- Deep dive into the syntax of XLookup, its features and parameters
- Using XLookup instead of HLookup