This article will go through examples of how to use XLookup vs HLookup to reference a by-column data table. We will highlight the advantages of using XLookup instead of Hlookup.
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 above data table. It is a typical by-column data table of auto manufacturer data. All of the information related to one manufacturer appears in one column. The columns are sorted alphabetically by manufacturer name.
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 from rows above the search row
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 Hlookup. On the left, we are searching for the value in cell C15 (Ford) in the range D4:H4 (Manufacturer Name row in our example) and looking for the result in the range D5:H5 (Headquarters). Results of the formula are highlighted.
Comparable HLookup
On the right, we are using XLookup to search for the value in cell C15. With HLookup, we specify a range that starts with the search range and continues through the results range. Then, we specify which row number (in this case 2) within the range to return.
One important difference is that if we insert a new row in the data table between rows 4 and 5, 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
HLookup, however, will break. Our intended result will not be in the second row anymore, but would be in the third row.
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 rows. We are searching for the value in cell C20 (Ford) in the range D4:H4 (Manufacturer Name row) and returning matchings row from the range D5:H7 (Headquarters, Worldwide Employees and Year Founded). Results of the formula are highlighted.
Note that the values are output in separate rows, corresponding with the structure of the data table.
For Example 2a, I wrapped a Transpose function around the XLookup, so the output is across columns instead of down rows. Now it looks exactly like we used multiple HLookup formulas.
Comparable HLookup
We can accomplish the same thing with HLookup, 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 HLookup than XLookup, adding to the spreadsheet’s complexity and to the time it takes to calculate.
Since we are using multiple HLookup formulas, we do have the ability to put the output in either separate rows or separate columns.
The Effect of Match Mode on XLookup vs HLookup
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 Tesla in the range D4:H4 (Manufacturer Name) and want to return the matching value in the range D5:H5 (Headquarters). Since Tesla 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 G.M. Example 5 specifies Match Mode 1 and returns the next larger item, Toyota.
In examples 6 and 7, we are searching for Alpha Romero, which does not exist. Specifying a Match Mode of 1 returns the next larger item, which in this case is Fiat. Specifying a March Mode of -1 looks for the next smaller item. Since there are no names smaller (alphabetically) then Alpha Romero, no match is returned. Note that in example 7, there is no Default Result specified, so the function returns a #N/A error.
Examples 8 and 9 show how wildcard characters can be used along with Match Mode of 2. In example 8, we are looking for a name that begins with “Fo”, and example 9 is looking for a six letter name ending in “ota.”
Comparable HLookup
The HLookup function has an optional third parameter: Approximate Match. If Approximate Match is set to TRUE (the default) then HLookup 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 C28 (Tesla) in the range D4:H28, and will return the value in row 2 of our range (Headquarters). Since Tesla is not in our data table, if we tell HLookup to use an approximate match it will return the headquarters of G.M., the next smaller value of manufacturer name.
For Approximate Match to work properly, the data table must be sorted in ascending order of the first row (row 4 in this example).
Returning values above the search row
This type of lookup would not be possible with HLookup. In examples 10 through 15 we are looking for year founded in the range D7:H7 and returning the corresponding manufacturer name found in the range D4:H4. We also vary Match Mode to see its effect on searching for items that do not exist in our data table.
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 VLookup
- Advantages of XLookup over VLookup with a by-row data table