VLOOKUP, HLOOKUP and XLOOKUP: Finding Data in a Table

The problem

Two tables, one shared key. The commercial team receives a weekly order export with product codes and quantities, but no prices. The pricing team owns the price list, with the same product codes, the product name, the pack and the list price. Someone has to bring the two together.

Done by hand, this is one of the most tedious and error-prone jobs in any spreadsheet. With a lookup function, it takes one formula copied down a column. Lookups are the bridge between tables, and they are the first functions worth mastering after the basic arithmetic ones.

Excel offers three of them for this job: VLOOKUP, the classic; HLOOKUP, its horizontal twin; and XLOOKUP, the modern standard that fixes most of the weaknesses of the other two.

How a lookup thinks

Every lookup works the way we search a dictionary. We look for a word in the list of entries, and once we find it, we read the definition next to it. Excel does the same:

1. it looks for a value in a column (or row) of a table;
2. when it finds the value, it moves to another column (or row) of the same table;
3. it returns what it finds there.

The analogy also shows where lookups are strong and where they are fragile. Unlike a person with a dictionary, Excel does not need the list to be sorted to find an exact match: if the value is there, Excel finds it. But the value must be written in exactly the same way. If the order export says Velorin 20mg and the price list says Velorin 20 mg, the search fails. And when the same value appears more than once, Excel stops at the first match and ignores the rest.

Every lookup needs three pieces of information: what to look for, where to look, and what to bring back. A fourth choice, often overlooked, decides whether Excel should accept only an exact match or settle for the closest one.

VLOOKUP

VLOOKUP searches down the first column of a table and returns a value from another column in the same row. The syntax is:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

The official argument names are not very telling, so it helps to translate them into plain English:

=VLOOKUP(what to look for, where to look, how many columns to the right, approximate match TRUE/FALSE)

Back to the example. The Price List sheet holds the product code in column A, the product name in column B, the pack in column C and the list price in column D, from row 2 to row 51. The Orders sheet has the product code in column A. To bring the list price into B2 of Orders, we write:

=VLOOKUP(A2,'Price List'!$A$2:$D$51,4,FALSE)

Excel reads this as: look for the code in A2 in the first column of the price list; when you find it, move to the fourth column of the table and return the list price; accept only an exact match. A code such as VEL-020 returns, for example, 1,245.50.

A few details make the difference between a formula that works and one that works only once.

The table must be locked with absolute references (the dollar signs) before the formula is copied down; otherwise the range slides with each row and the lookup starts missing values for no apparent reason. When the sheet layout allows it, whole columns are a quick alternative: 'Price List'!$A:$D never needs updating when new products are added at the bottom.

The column index counts from the first column of the table, not from column A of the sheet. An index of 1 returns the value we searched for, which is rarely useful; an index of 4 returns the fourth column of the table. Negative values are not allowed, and this is the structural limit of VLOOKUP: it can only look to the right of the search column, never to the left.

The last argument deserves the most attention. FALSE forces an exact match and is the right choice in the vast majority of cases. TRUE, or simply leaving the argument out, tells Excel to settle for an approximate match. Yes... omitting the argument means TRUE. This default is behind countless wrong results that look perfectly plausible on screen.

Approximate match: tiers without nested IFs

There is one situation where the approximate match is exactly what we want: assigning a value to a band. Suppose the commercial policy grants a volume discount based on the number of units ordered. The tiers sit in H1:I6:

With the quantity ordered in E2, the discount is:

=VLOOKUP(E2,$H$2:$I$6,2,TRUE)

With an approximate match, Excel returns the largest threshold that is less than or equal to the value searched. The thresholds must therefore be read as "at least", not "up to": 3,200 units fall in the 1,000 band and get 2.5%; 12,000 units get 7.5%. The one strict requirement is that the first column is sorted in ascending order. If it is not, VLOOKUP does not raise an error: it simply returns the wrong band.

Compared with a chain of nested IF functions, this approach is shorter, easier to read and much easier to maintain. A new tier is one new row in the table, not a rewrite of the formula. The same logic works for any scale: ratings, risk classes, price corridors, commission rates.

HLOOKUP

HLOOKUP works in exactly the same way, rotated by 90 degrees. It searches along the first row of a table and returns a value from a row further down, in the same column:

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Imagine a small parameter table where each market sits in a column: the market names in B1:F1 (Market A to Market E), the VAT rate on medicines in row 2, the wholesaler margin in row 3 and the pharmacy margin in row 4. With Market C typed in H2, the wholesaler margin is:

=HLOOKUP(H2,$B$1:$F$4,3,FALSE)

Keeping the market in its own cell, rather than typing it inside the formula, means the same formula answers for any market: change H2 and the result follows. Excel finds Market C in the first row of the table, moves down to the third row and returns the value. Everything said about VLOOKUP applies here too: lock the range, count rows from the top of the table, and use FALSE unless a band lookup is really intended.

HLOOKUP is used far less than VLOOKUP, and for good reason. A worksheet has 1,048,576 rows but only 16,384 columns, so tables that grow to the right hit a wall much sooner than tables that grow downward. As a general rule, data tables should grow down, never across. HLOOKUP remains handy for small, horizontal parameter tables like the one above, or for headers in a report laid out by period.

XLOOKUP: the modern standard

Anyone who has used VLOOKUP for years knows its three weak spots. The result column is identified by a number, so inserting a column in the table silently breaks every formula that points to it. The search goes in one direction only, so data to the left of the key cannot be reached without workarounds such as INDEX and MATCH. And a missing value returns #N/A, which spreads through every formula that depends on it unless each lookup is wrapped in IFERROR.

XLOOKUP solves all three with a different design. Instead of one table and a column number, it takes two separate ranges: where to look, and what to return. The syntax is:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

The price lookup from the beginning of this article becomes:

=XLOOKUP(A2,'Price List'!$A$2:$A$51,'Price List'!$D$2:$D$51,"Not listed")

The formula reads almost like a sentence: look for A2 in the code column, return the matching value from the price column, and if there is no match, write Not listed. If someone inserts a new column between the code and the price, the formula adjusts itself, because it refers to the price column directly and not to its position.

Looking to the left

Because the two ranges are independent, the return column can sit anywhere, including to the left of the search column. To find the product code from the product name typed in F2:

=XLOOKUP(F2,'Price List'!$B$2:$B$51,'Price List'!$A$2:$A$51,"Not listed")

With VLOOKUP this would require rearranging the table or switching to INDEX and MATCH.

Handling missing values

The fourth argument, if_not_found, defines what to return when there is no match: a text such as Not listed, a zero, or an empty string. It replaces the classic IFERROR(VLOOKUP(...),"Not listed") construction, and it is more precise. IFERROR hides every error, including those that point to a real problem in the formula; if_not_found only covers the case where the value is simply not there.

Approximate match without sorting

The fifth argument, match_mode, sets how strict the search is:

• 0, the default: exact match only;
• -1: exact match, or the next smaller value if there is none;
• 1: exact match, or the next larger value if there is none;
• 2: wildcard match, where * stands for any sequence of characters and ? for a single character.

The discount tiers become:

=XLOOKUP(E2,$H$2:$H$6,$I$2:$I$6,,-1)

Two differences from VLOOKUP stand out. The approximate match is declared explicitly with -1, instead of hiding behind a TRUE (or behind a missing argument). And with the default search mode, XLOOKUP does not need the thresholds to be sorted, so a tier table that someone reorders does not quietly start returning the wrong band. Keeping the table sorted is still good practice... it is simply easier to read.

The wildcard mode is useful when only part of a text is known. With Velorin* typed in F5, =XLOOKUP(F5,'Price List'!$B$2:$B$51,'Price List'!$D$2:$D$51,"Not listed",2) returns the price of the first product whose name starts with Velorin.

Finding the most recent entry

The sixth argument, search_mode, sets the direction of the search. The default, 1, searches from the first item to the last; -1 searches from the last to the first. This is what makes XLOOKUP so useful with logs and histories.

Consider a Price Changes sheet that records every price change in date order: date in column A, product code in column B, new price in column D. A normal lookup would return the first price ever recorded for a product. Searching backwards returns the latest one:

=XLOOKUP(G2,'Price Changes'!$B$2:$B$200,'Price Changes'!$D$2:$D$200,"No change",0,-1)

Search modes 2 and -2 also exist: they run a binary search on data sorted in ascending or descending order. They matter only on very large tables, and they return wrong results if the data is not sorted, so the default modes are the safer choice.

Two-way lookups

The most elegant use of XLOOKUP is a lookup inside another lookup. Take a price matrix with products down column A (A2:A20), markets across row 1 (B1:F1) and prices in B2:F20. With the product in G2 and the market in H2:

=XLOOKUP(G2,$A$2:$A$20,XLOOKUP(H2,$B$1:$F$1,$B$2:$F$20))

The formula reads from the inside out. The inner XLOOKUP finds the market in the header row and returns the whole column of prices for that market. The outer XLOOKUP finds the product in column A and picks the matching price from that column. One formula, no helper columns, no concatenated keys.

Returning several columns at once

The return array does not have to be a single column. Pointing it at several adjacent columns returns all of them in one go:

=XLOOKUP(A2,'Price List'!$A$2:$A$51,'Price List'!$B$2:$D$51,"Not listed")

This single formula returns the product name, the pack and the list price, spilling across three cells. The cells to the right must be empty; if they are not, Excel shows a #SPILL! error until space is freed.

Which one to use

XLOOKUP is available in Microsoft 365, Excel 2021 and Excel 2024, but not in Excel 2016 or Excel 2019. For new work in a current version, it should be the default choice: it is more readable, more robust to changes in the table layout and more expressive in the edge cases. VLOOKUP remains relevant for files that must be opened by colleagues or partners on older versions, and for the many existing models built around it, which anyone working in Excel will inherit sooner or later. HLOOKUP is worth knowing for small horizontal tables, and little more.

Common mistakes

• Leaving out the last argument of VLOOKUP or HLOOKUP. The default is an approximate match. On unsorted data, the result is wrong and nothing warns you.
• Not locking the table range. Copied down, the range shifts one row at a time and the lookup starts failing halfway through the list.
• Hard-coding the column index. Insert a column in the table and every VLOOKUP returns the wrong field. XLOOKUP avoids the problem; with VLOOKUP, check formulas after every change to the table layout.
• Mismatched ranges in XLOOKUP. The lookup array and the return array must have the same number of rows (or columns). If they do not, XLOOKUP returns #VALUE!.
• Swapping lookup_array and return_array. Excel raises no error; it simply returns nonsense. When an XLOOKUP gives strange numbers, this is the first thing to check.
• Invisible differences in the key. A trailing space, a code stored as text in one table and as a number in the other, or a non-breaking space pasted from a web page: the values look identical but do not match.
• Leaving thousands of live lookups in a static file. When the matched data will not change again, copying and pasting the results as values makes the file lighter and faster.

For further reading

• VLOOKUP function - Microsoft Support
• HLOOKUP function - Microsoft Support
• XLOOKUP function - Microsoft Support