Two-Way Lookup in Excel: INDEX MATCH MATCH, XLOOKUP
· 8 min read
A two way lookup Excel formula finds the value where a row and a column meet in a grid, such as the price of one product in one pack size. Use INDEX for the grid and one MATCH for each direction:
=INDEX(grid, MATCH(row_label, row_labels, 0), MATCH(column_label, column_headers, 0))The first MATCH says which row, the second says which column, and INDEX returns the cell where they cross. People call this INDEX MATCH MATCH. If you have Excel 2021, Microsoft 365 or Excel for the web, two nested XLOOKUPs do the same job. Both are below, plus the version for rate tables where the row is a band ("2 kg and up") instead of an exact label.
If INDEX MATCH is new to you, read how to use INDEX MATCH first. This guide picks up where its two-way section stops.
The example grid
A price list for four products in three pack sizes. G2 and G3 hold the product and pack to find. The rows below them build the lookup step by step, then show three ways to write it and one broken version.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Single | 6-pack | 12-pack | Lookup | Value | |
| 2 | Copy paper | $6.50 | $36.00 | $69.00 | Product to find | Hand soap | |
| 3 | Paper towels | $3.20 | $18.00 | $34.00 | Pack to find | 6-pack | |
| 4 | Hand soap | $4.75 | $27.00 | $52.00 | Row (MATCH) | 3 | |
| 5 | Coffee beans | $14.00 | $80.00 | $155.00 | Column (MATCH) | 2 | |
| 6 | INDEX MATCH MATCH | $27.00 | |||||
| 7 | Nested XLOOKUP | $27.00 | |||||
| 8 | VLOOKUP with MATCH | $27.00 | |||||
| 9 | AI's formula | $34.00 |
INDEX MATCH MATCH, step by step
Find the row
=MATCH(G2, A2:A5, 0)Result: 3. Hand soap is the 3rd label in A2:A5. The 0 means exact match, which is what you want for names and codes.
Find the column
=MATCH(G3, B1:D1, 0)Result: 2. 6-pack is the 2nd header in B1:D1. MATCH works across a row just as it works down a column.
Let INDEX return the crossing cell
=INDEX(B2:D5, MATCH(G2, A2:A5, 0), MATCH(G3, B1:D1, 0))Result: 27.00, the cell in row 3 and column 2 of the price grid.
The ranges have to line up. The row labels A2:A5 cover the same rows as the grid B2:D5, and the headers B1:D1 cover the same columns. That is why the header range starts at B, not A: the grid starts at B, so position 1 must mean column B. Change G2 to Coffee beans and G3 to 12-pack, and the answer becomes 155.00.
Why not hard-code the column?
You could write =INDEX(B2:D5, MATCH(G2, A2:A5, 0), 2), but then the formula breaks the day someone adds a "3-pack" column. With a MATCH for each direction, the formula finds both positions every time, so new rows and columns are picked up as long as they sit inside the ranges.
Nested XLOOKUP
XLOOKUP can return a whole row of a grid. Put one XLOOKUP inside another and the inner one picks the row, the outer one picks the column from that row:
=XLOOKUP(G3, B1:D1, XLOOKUP(G2, A2:A5, B2:D5))Result: 27.00. Reading from the inside out:
XLOOKUP(G2, A2:A5, B2:D5)finds Hand soap and returns its whole row of prices: 4.75, 27, 52.- The outer XLOOKUP looks for 6-pack in the headers B1:D1 and returns the matching item from that row.
You can also nest it the other way round, with the inner XLOOKUP returning a column for the pack size and the outer one picking the product. Both give the same answer. XLOOKUP defaults to exact match, so there is no 0 to forget, and it returns #N/A if either label is missing. Add a fourth argument, such as "Not listed", to the outer XLOOKUP if you want a message instead. XLOOKUP in Excel covers the other arguments.
XLOOKUP does not exist in Excel 2019 or earlier, where it shows #NAME?. If the file is shared widely, INDEX MATCH MATCH is the safer choice.
VLOOKUP with MATCH
Older workbooks often do the same thing with VLOOKUP and a MATCH for the column number:
=VLOOKUP(G2, A1:D5, MATCH(G3, A1:D1, 0), FALSE)Result: 27.00. Notice that the header range here starts at A1, not B1. VLOOKUP counts columns from the first column of its table, so the product names are column 1 and 6-pack is column 3. Mixing up the two conventions is the most common reason a VLOOKUP and MATCH returns the price from the next column. See how to use VLOOKUP for the rest of its rules, or VLOOKUP vs XLOOKUP vs INDEX MATCH for a side-by-side comparison.
Approximate match: rate tables with bands
Many grids have bands down the side instead of exact labels. A shipping rate card lists the starting weight of each band, and each parcel belongs to the highest band it reaches. For that row, use MATCH with 1 instead of 0:
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | From kg | Zone 1 | Zone 2 | Zone 3 | Parcel | Weight kg | Zone | Rate | XLOOKUP rate | |
| 2 | 0 | $4.50 | $6.00 | $7.50 | P-1 | 1.2 | Zone 2 | $6.00 | $6.00 | |
| 3 | 2 | $6.00 | $8.00 | $10.00 | P-2 | 5 | Zone 1 | $9.00 | $9.00 | |
| 4 | 5 | $9.00 | $12.00 | $15.00 | P-3 | 7.8 | Zone 3 | $15.00 | $15.00 | |
| 5 | 10 | $14.00 | $18.00 | $23.00 | P-4 | 12 | Zone 2 | $18.00 | $18.00 | |
| 6 | 20 | $22.00 | $28.00 | $35.00 | P-5 | 31 | Zone 3 | $35.00 | $35.00 | |
| 7 | P-6 | 2 | Zone 3 | $10.00 | $10.00 |
The rate in I2, filled down:
=INDEX($B$2:$D$6, MATCH(G2, $A$2:$A$6, 1), MATCH(H2, $B$1:$D$1, 0))The results are 6.00, 9.00, 15.00, 18.00, 35.00 and 10.00. A few to check by hand:
- P-1 weighs 1.2 kg. The highest "From" value it reaches is 0, so it is in the first band.
- P-2 weighs exactly 5 kg, so it reaches the 5 kg band. Boundaries belong to the band that starts there.
- P-5 weighs 31 kg, more than the last "From" value, so it stays in the top band.
The rows use approximate match (1) and the columns still use exact match (0), because zones are names, not numbers. Approximate match needs the band column sorted from smallest to largest. If it is not sorted, MATCH can return the wrong band without any error.
In column J, the XLOOKUP version uses -1 as the match mode, which means "exact match or the next smaller item":
=XLOOKUP(H2, $B$1:$D$1, XLOOKUP(G2, $A$2:$A$6, $B$2:$D$6, , -1))The empty fourth argument skips "if not found". XLOOKUP with -1 does not need the bands sorted, which makes it more forgiving, but sorted bands are still easier for a person to read.
Common mistakes
Row and column swapped. The AI's formula in G9 of the first sheet puts the pack size MATCH where the row number goes and the product MATCH where the column goes:
=INDEX(B2:D5, MATCH(G3, B1:D1, 0), MATCH(G2, A2:A5, 0))It returns 34.00, the price of a 12-pack of paper towels, instead of 27.00. There is no error, and the result looks like a real price because it is one. INDEX always takes the row first, then the column. If a product near the bottom of the list is looked up, the swapped version runs out of columns and shows #REF!, which is often the first sign anything is wrong.
Header range that does not match the grid. With INDEX, the headers must start in the same column as the grid (B1:D1 for B2:D5). With VLOOKUP, they must start in the table's first column (A1:D1). Using the wrong one shifts every answer by one column.
Forgetting the 0. MATCH(G2, A2:A5) uses approximate match by default. On an unsorted list of names it can return a neighbour's row.
Unsorted bands with MATCH type 1. If someone adds a band at the bottom of the rate card out of order, parcels land in the wrong band. Keep the bands sorted, or use XLOOKUP with -1.
Ranges that slide when filled down. In the rate card, every range is locked with $ and only G2 and H2 move. Without the $ signs, the grid slides down one row per parcel. See absolute references.
The lookup drills on the practice page include grids like these on a full year of sales data.
FAQ
How do I look up a value by row and column in Excel?
Use =INDEX(grid, MATCH(row_value, row_labels, 0), MATCH(column_value, column_headers, 0)). The two MATCH functions find the row and column positions, and INDEX returns the cell where they meet.
Can XLOOKUP do a two-way lookup?
Yes. Nest one inside the other: =XLOOKUP(column_value, headers, XLOOKUP(row_value, row_labels, grid)). It needs Excel 2021, Microsoft 365 or Excel for the web.
How do I do a two-way lookup with approximate match?
Use match type 1 in the MATCH for the banded direction and 0 for the other: =INDEX(grid, MATCH(weight, bands, 1), MATCH(zone, headers, 0)). The bands must be sorted smallest to largest.
Why does my INDEX MATCH MATCH return the wrong value?
Usually the row and column MATCH are in the wrong order, or the header range starts one column before the grid. Check each MATCH on its own in a spare cell.
What is the difference between INDEX MATCH and INDEX MATCH MATCH?
INDEX MATCH looks up a value in one column. INDEX MATCH MATCH adds a second MATCH for the column, so it can look up a value anywhere in a grid.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
- Look up a target by month and region
- Every target belongs to the next regionAudit
- Discounts by order size and segment
- Which discount tier is each order in?