| Concept | Description |
|---|---|
| VLOOKUP and HLOOKUP | |
| Lookup as a Table Join | Pulling fields from a second sheet by a shared key is the same operation as a database join |
| VLOOKUP | Searches down the first column of a range and returns a column counted rightward by number |
| The Fourth Argument | FALSE demands an exact match; TRUE or omitting it permits approximate matching and silent errors |
| VLOOKUP's Three Structural Faults | Cannot search leftward, uses a hard-coded column number, and defaults to approximate matching |
| HLOOKUP | The horizontal equivalent, usually a sign the data should be unpivoted instead |
| INDEX, MATCH, XLOOKUP | |
| MATCH and INDEX | MATCH returns a position within a range; INDEX returns the value at a given position |
| INDEX with MATCH | Independent search and return ranges, so direction is free and inserted columns do not break it |
| XLOOKUP | Exact match by default, separate arrays, a built-in not-found argument, multi-column returns |
| XLOOKUP's Match and Search Modes | match_mode sets exact, next smaller, next larger or wildcard; search_mode sets direction |
| Two-Way Lookup | INDEX with two MATCH calls, or nested XLOOKUP, for a row and column intersection |
| Practice and Diagnosis | |
| Approximate Match and Banding | Approximate matching legitimately assigns a continuous value to a sorted band table |
| Diagnosing #N/A | Trailing spaces, number against text, relative ranges, and genuinely absent values |
| Choosing a Lookup Function | XLOOKUP where available, INDEX with MATCH otherwise, VLOOKUP only for throwaway work |
15 Lookup and Reference Functions
FarmData records a crop code, PDY. The crop’s name, its season, and its minimum support price live on a different sheet. Joining the two is the single most common operation in applied spreadsheet work, and it is the same operation a database calls a join and R calls a merge.
Two lookup sheets are used throughout this section.
CropLookup, rows 2 to 9:
| Column | Field |
|---|---|
| A | CropCode |
| B | CropName |
| C | Season |
| D |
MSP, rupees per quintal |
DistrictLookup, rows 2 to 6:
| Column | Field |
|---|---|
| A | District |
| B | Zone |
| C | ExtensionOfficer |
15.1 VLOOKUP
VLOOKUP searches down the first column of a range and returns a value from a column to its right.
=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)To pull the crop name into FarmData, where C2 holds the crop code:
=VLOOKUP(C2, CropLookup!$A$2:$D$9, 2, FALSE)The four arguments, in order: the value being searched for, the range to search, the column number within that range to return (counting the search column itself as 1), and whether an approximate match is acceptable.
That fourth argument is the one that causes damage. FALSE demands an exact match and returns #N/A when there is none. TRUE, or leaving the argument out entirely, permits an approximate match, which on unsorted data returns whatever it happens to land on. It is wrong quietly, which is worse than being wrong loudly. Use FALSE unless deliberately building a banded lookup, covered further down.
Note the absolute reference on the table array. Copied down 60 rows with a relative reference, the search range would drift down a row at a time and the last farms would be searching a range that no longer contains the lookup table.
More columns, same pattern:
=VLOOKUP(C2, CropLookup!$A$2:$D$9, 3, FALSE) Season
=VLOOKUP(C2, CropLookup!$A$2:$D$9, 4, FALSE) MSPWrapped so a missing code reads as something sensible:
=IFERROR(VLOOKUP(C2, CropLookup!$A$2:$D$9, 2, FALSE), "Code not found")15.1.1 Where VLOOKUP Falls Down
Three structural problems, all of which have bitten every regular Excel user.
It cannot look to the left. The search column must be the first column of the range. Given a sheet where CropName sits left of CropCode, VLOOKUP cannot return the name from the code without physically rearranging the sheet.
The column index is a hard-coded number. =VLOOKUP(C2, CropLookup!$A$2:$D$9, 3, FALSE) returns the third column. Insert a new column anywhere inside that range and the formula still returns the third column, which is now a different field. Nothing breaks visibly. The sheet simply starts reporting the wrong thing.
Approximate match is the default. Omitting the fourth argument gives approximate matching, which on unsorted data produces plausible-looking nonsense.
INDEX with MATCH, and XLOOKUP on newer versions, fix all three.
15.2 HLOOKUP
The horizontal equivalent, searching across the first row of a range rather than down the first column.
=HLOOKUP(lookup_value, table_array, row_index_num, range_lookup)
=HLOOKUP("Q3", SalesWide!$B$1:$E$20, 5, FALSE)It is rarely the right answer. A sheet that needs HLOOKUP is usually a sheet laid out wide when it should be laid out long, and the better fix is to unpivot it, which Power Query does in three clicks.
15.3 INDEX and MATCH
Two functions that each do one job. MATCH returns the position of a value within a range. INDEX returns the value at a given position.
=MATCH("PDY", CropLookup!$A$2:$A$9, 0) Returns 1, the row position
=INDEX(CropLookup!$B$2:$B$9, 1) Returns the first crop nameCombined, the position found by MATCH feeds straight into INDEX:
=INDEX(CropLookup!$B$2:$B$9, MATCH(C2, CropLookup!$A$2:$A$9, 0))The third argument of MATCH works like VLOOKUP’s fourth but with different values: 0 for exact match, 1 for the largest value less than or equal to the target (requires ascending sort), -1 for the smallest value greater than or equal (requires descending sort). Use 0.
This construction solves every one of VLOOKUP’s problems. The return range and the search range are specified independently, so the return column can sit anywhere, left or right. Because each is an actual range reference rather than a counted offset, inserting a column shifts both references automatically and the formula keeps returning the right field.
It reads less naturally than VLOOKUP, which is the only reason VLOOKUP is still more widely taught. On Excel 2019 and earlier, INDEX with MATCH is the right default for any lookup that will be maintained by someone other than its author.
15.4 XLOOKUP
Available on Microsoft 365 and Excel 2021 onward, XLOOKUP replaces all of the above.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])The same crop name lookup:
=XLOOKUP(C2, CropLookup!$A$2:$A$9, CropLookup!$B$2:$B$9)Exact match is the default, so nothing has to be remembered. The lookup and return arrays are separate, so direction is irrelevant. And the fourth argument handles the not-found case without wrapping anything:
=XLOOKUP(C2, CropLookup!$A$2:$A$9, CropLookup!$B$2:$B$9, "Code not found")Returning several columns at once, by giving a multi-column return array, spills the result across adjacent cells:
=XLOOKUP(C2, CropLookup!$A$2:$A$9, CropLookup!$B$2:$D$9)That one formula returns crop name, season, and MSP together.
15.4.1 XLOOKUP’s Optional Arguments
The fifth argument sets match behaviour, the sixth sets search direction.
match_mode |
Behaviour |
|---|---|
0 |
Exact match. The default |
-1 |
Exact, or the next smaller value |
1 |
Exact, or the next larger value |
2 |
Wildcard match, where * and ? are active |
search_mode |
Behaviour |
|---|---|
1 |
First to last. The default |
-1 |
Last to first, which returns the most recent match in a log |
2 |
Binary search, ascending order assumed |
-2 |
Binary search, descending order assumed |
Searching backwards through a price history to find the latest recorded price for a crop:
=XLOOKUP(C2, MonthlyPrices!$B$2:$B$145, MonthlyPrices!$D$2:$D$145, "No record", 0, -1)15.5 Two-Way Lookup
Finding a value at the intersection of a row and a column, for instance the price of a given crop in a given month from a grid laid out with crops down the side and months across the top.
With INDEX and two MATCH calls, one for each axis:
=INDEX($B$2:$M$9, MATCH($A12, $A$2:$A$9, 0), MATCH(B$11, $B$1:$M$1, 0))With nested XLOOKUP, where the inner call returns a row and the outer picks the cell from it:
=XLOOKUP($A12, $A$2:$A$9, XLOOKUP(B$11, $B$1:$M$1, $B$2:$M$9))Watch the mixed references. $A12 pins the column so the formula can fill rightward, B$11 pins the row so it can fill downward, and the lookup ranges are fully absolute. Getting these wrong is the reason a filled grid returns correct values in the first cell and nonsense everywhere else.
15.6 Approximate Match and Banding
Approximate matching has one legitimate use: assigning a continuous value to a band. Yield grades, price slabs, subsidy tiers.
Build the band table with the lower bound of each band, sorted ascending:
| A | B | |
|---|---|---|
| 1 | LowerBound |
Grade |
| 2 | 0 | Poor |
| 3 | 30 | Fair |
| 4 | 40 | Good |
| 5 | 50 | Excellent |
Then look up the yield with approximate matching switched on:
=VLOOKUP(J2, $A$2:$B$5, 2, TRUE)
=XLOOKUP(J2, $A$2:$A$5, $B$2:$B$5, "Out of range", -1)A yield of 43 finds no exact match, falls back to the largest bound not exceeding it (40), and returns Good.
The band table must be sorted ascending for VLOOKUP with TRUE. An unsorted table returns wrong answers with no error, which is the single most dangerous behaviour in Excel. XLOOKUP with match_mode of -1 does not require sorting, another reason to prefer it where available.
15.7 Why a Lookup Returns #N/A
Six causes, in roughly the order they occur.
A trailing space on one side. "Guntur " does not equal "Guntur". Test with =LEN(C2) on both sides and fix with TRIM.
Number against text. A code stored as the number 1001 will not match the text "1001". =ISNUMBER(C2) on each side reveals the mismatch, and Text to Columns converts the text side.
The search value is not in the first column of a VLOOKUP range. The range must begin at the search column.
The range is relative and has drifted during the fill. Check a formula near the bottom of the column, not the top.
A genuinely absent value. A crop code present in FarmData but missing from CropLookup is a data problem, not a formula problem, and hiding it with IFERROR means it never gets fixed.
Non-printing characters from an external export. =TRIM(CLEAN(C2)) handles most of them, and =CODE(RIGHT(C2,1)) identifies the culprit when it does not.
A useful diagnostic, since it separates “no match” from “formula wrong”:
=COUNTIF(CropLookup!$A$2:$A$9, C2)Zero means the value genuinely is not in the lookup range. Anything above zero means it is there and the formula is at fault.
15.8 Choosing Between Them
| Situation | Use |
|---|---|
| Microsoft 365 or Excel 2021 and later | XLOOKUP |
| Excel 2019 or earlier, maintained sheet |
INDEX with MATCH
|
| Excel 2019 or earlier, quick throwaway |
VLOOKUP with FALSE
|
| Return column is left of the search column |
INDEX/MATCH or XLOOKUP
|
| Several columns returned at once |
XLOOKUP with a multi-column return array |
| Banded assignment from a continuous value | Either, with approximate match on |
| Most recent entry in a log |
XLOOKUP with search_mode of -1
|
All of this is a table join. In R the same operation is merge() or dplyr::left_join(), it handles the whole column in one statement rather than one formula per row, and it raises an error when keys fail to match rather than scattering #N/A down a column. That comparison is worked through in From Excel to R.