Lookups: VLOOKUP, INDEX and MATCH, XLOOKUP

In depth

VLOOKUP

VLOOKUP (“vertical lookup”) searches down the first column of a range and returns a value from a column to its right. It takes four arguments.

So Ana’s VLOOKUP version is =VLOOKUP(B2,$H$2:$L$12,5,FALSE). It returns the same costs as her XLOOKUP. Without the dollar signs of an absolute reference, filling it down would slide the range to H3:L13, H4:L14 and so on, and the lower rows would stop finding the products near the top of the list.

VLOOKUP has two more weaknesses. First, the lookup value must be in the first column of table_array, so it can’t return anything to the left of the key: to find an order id from a customer number, the customer column must come first. Second, the column number is a constant typed into the formula. If someone inserts a column for supplier codes between line and size_g, Excel widens the range to $H$2:$M$12 but leaves the 5 alone, so the formula now returns column L, which holds the price: Gift Box Trio comes back at $58.00 instead of $20.00. A column number larger than the range is wide gives #REF!.

HLOOKUP is the same function turned on its side: it searches across the top row of a range and returns a value from a row below, with the same fourth argument and the same default. You need it only for tables laid out sideways, such as a rate card with months across the top.

INDEX and MATCH

MATCH finds a position: =MATCH("Cold Brew Pack",$H$2:$H$12,0) returns 10, because Cold Brew Pack is the 10th item in H2:H12. INDEX returns what is at a position: =INDEX($L$2:$L$12,10) returns $8.50, the 10th value in L2:L12. Put together, =INDEX($L$2:$L$12,MATCH(B2,$H$2:$H$12,0)) finds the order’s product in column H and returns the cost from the same position in column L.

The pair fixes both of VLOOKUP’s weaknesses. The return range is separate from the key range, so it can lie to the left. And because both are ordinary references, inserting a column shifts them along with the data, so the formula keeps pointing at the cost. One MATCH can also feed several INDEX formulas, which matters in a large workbook.

INDEX can also take a row number and a column number, which gives a two-way lookup: one MATCH finds the row, another the column. The table below counts Ana’s spring orders by month and channel. To fetch May’s web orders, =INDEX($B$2:$C$5,MATCH("May",$A$2:$A$5,0),MATCH("web",$B$1:$C$1,0)) returns 23. Point the two lookup values at cells holding a month and a channel, and the same formula answers any combination.

XLOOKUP and XMATCH

XLOOKUP keeps the lookup range and the return range apart, as INDEX and MATCH do, and matches exactly unless told otherwise. Its first three arguments are required.

Three of these options solve everyday problems. A not-found value makes a missing key visible: =XLOOKUP(B2,$H$2:$H$12,$L$2:$L$12,"not on price list"). Searching from the last item finds the most recent match in a list kept in date order: in Lantern’s events sheet, with event ids in column A and clients in column C, =XLOOKUP("Pier Nine Studio",$C$2:$C$77,$A$2:$A$77,,0,-1) returns EV-068, the client’s latest event (9 December), while searching from the first item returns EV-003. And a return range of several columns brings back several values at once: =XLOOKUP("Cold Brew Pack",$H$2:$H$12,$I$2:$L$12) returns the line, size, price and cost, which spill into four cells side by side.

XLOOKUP also works on tables laid out across a row, so it replaces HLOOKUP too. XMATCH is MATCH with XLOOKUP’s arguments: it returns a position, matches exactly by default, and takes the same match and search modes. Microsoft’s own two-way example combines them: =INDEX($B$2:$C$5,XMATCH("May",$A$2:$A$5),XMATCH("web",$B$1:$C$1)) gives the same 23 as the INDEX and MATCH version.

Approximate match: lookups into bands

A band table lists the lower bound (“from”) of each band in its first column, sorted from smallest to largest, with each band’s result beside it. An approximate match returns the result of the last band whose lower bound is not above the lookup value. The formula card below states the rule; the figure above shows it working.

With the order value in B2, any of three formulas returns the delivery charge: =VLOOKUP(B2,$F$2:$G$3,2,TRUE), =XLOOKUP(B2,$F$2:$F$3,$G$2:$G$3,,-1) (match mode −1: exact match or else the next smaller item) and =INDEX($G$2:$G$3,MATCH(B2,$F$2:$F$3,1)). The size band is the same idea with four rows: =XLOOKUP(B2,$I$2:$I$5,$J$2:$J$5,,-1).

The boundary needs care. B01’s rule is free delivery on orders over $35, and order values are whole cents, so the free band starts at $35.01 and an order of exactly $35.00 pays $4.95. Typing 35 in the table would hand a $35.00 order free delivery that the rule doesn’t give. Test every boundary with a value just below it, one exactly on it and one just above it: 34.99, 35.00 and 35.01 here. Test the bottom too: a value below the first lower bound, such as a −12.60 refund, has no band and returns #N/A.

Keep band tables sorted. Microsoft documents that VLOOKUP’s approximate match and MATCH with match_type 1 assume the first column is sorted from smallest to largest, and that XLOOKUP’s binary search modes give invalid results on unsorted data. XLOOKUP’s default search reads every item, so its match_mode −1 copes with an unsorted table, but a sorted table is the one a reader can check at a glance. Why not use IF? =IF(B2>35,0,4.95) works for two bands, but four size bands need nested IFs with the thresholds buried inside the formula, while a band table puts the rule where anyone can read and change it.

Why a lookup returns #N/A, or the wrong answer

An #N/A from an exact-match lookup is an error value that means “no key equal to this one”. Microsoft’s list of the usual causes covers four of the rows below: the value really isn’t there, the two sides hold different data types, there are extra spaces, and the match type is wrong. The wrong match type, the unlocked range and the duplicate key are the dangerous ones, because they can give a plausible number instead of an error. Two tests help with types: =ISTEXT(B2) is TRUE when B2 holds text, and =ISNUMBER(B2) is TRUE when it holds a number.

Handle a genuine miss on purpose. XLOOKUP’s if_not_found, or =IFNA(VLOOKUP(B2,$H$2:$L$12,5,FALSE),"not on price list") in versions without XLOOKUP, labels the rows that need attention. Don’t wrap lookups in IFERROR to make the errors go away: IFERROR also hides a #REF! or #VALUE! from a broken formula, and a cost replaced by 0 or a blank understates the product cost without anyone noticing. The functions-and-logic lesson compares IFERROR and IFNA.

Duplicate keys deserve a check of their own. Microsoft’s developer reference for VLOOKUP says that when two or more values in the first column match, the first value found is used; MATCH with 0 finds the first value exactly equal to the lookup value; and XLOOKUP searches from the first item unless told otherwise. A price list with House Blend 250g twice, at two costs, would return the first cost for every order, right or not. Fill =COUNTIF($H$2:$H$12,H2) down beside the key column: any result above 1 is a duplicate key to resolve (B02’s duplicates lesson covers how).

Which lookup to use

Use XLOOKUP when everyone who opens the workbook has a version that supports it; use INDEX and MATCH when someone may open it in Excel 2019 or earlier; and learn to read VLOOKUP, because it fills the workbooks you will inherit. Whichever you write, check the match type, lock the table range and test a key you know.