Lookup & reference › INDEX, MATCH94 / 144

Progress is saved on this device. Sign in or sign up free to keep it in your account.

HardFreeINDEXMATCH

INDEX + MATCH + MATCH: rates from a two-way table

The admin of an apple chips souvenir shop in Batu has a per-kg shipping rate table: destination cities down the side, courier service types across the top. For each parcel she has to find where the city row meets the service column. The shipping column already multiplies that rate by the parcel weight.

Task

Fill D2:D6 with the per-kg rate for each parcel's city (column B) and service (column C), from the table H1:K7 (cities in H2:H7, services in I1:K1, rates in I2:K7). Use INDEX with two MATCH calls: one for the row, one for the column.

Cells to fillD2:D6

Required functionsINDEXMATCH

Expected result

Loading the expected result…

Check answerCtrlShiftEnter
Fill downCtrlD
Previous / next exerciseAlt←→
Leave the workspaceEscTab

Hint 1
INDEX(table, row_number, column_number) returns the cell where a given row and column meet.
Hint 2
The row number = the city's position in $H$2:$H$7, the column number = the service's position in $I$1:$K$1. Find both with MATCH(…, …, 0).
Hint 3
Pattern: =INDEX($I$2:$K$7, MATCH(B2, …, 0), MATCH(C2, …, 0)).

Reveal them one at a time. The last hint nearly gives the answer away.

Give it a try first. The solution unlocks automatically after your first check.

Answer

D2:D6

=INDEX($I$2:$K$7,MATCH(B2,$H$2:$H$7,0),MATCH(C2,$I$1:$K$1,0))

Write it in D2, then fill down to D6.

Explanation

=INDEX($I$2:$K$7,MATCH(B2,$H$2:$H$7,0),MATCH(C2,$I$1:$K$1,0)) finds two positions, the city row and the service column, and INDEX returns the rate where they meet. The MATCH ranges must line up with the rate table: the city list starts on row 2 like I2:K7, and the service headers line up with columns I to K. A frequent mistake is including the city name column in the INDEX range, which shifts every result by one column. In Excel 365, two nested XLOOKUP calls can also do a two-way lookup.

Starting worksheet: Sheet1
Row	A	B	C	D	E	F	H	I	J	K
1	Waybill	Destination	Service	Rate/kg	Weight (kg)	Shipping	City	REG	EXP	ECO
2	BTU-881	Surabaya	EXP		2.0		Jakarta	12,000	22,000	9,000
3	BTU-882	Medan	REG		1.5		Surabaya	8,000	15,000	6,000
4	BTU-883	Jakarta	ECO		4.0		Bandung	11,000	20,000	8,500
5	BTU-884	Denpasar	REG		3.0		Denpasar	15,000	27,000	12,000
6	BTU-885	Makassar	EXP		1.0		Medan	26,000	41,000	21,000
7							Makassar	28,000	44,000	23,000
8
9
10
11
12
13
14

Fill in the requested cell, then press Check answer.

Validation result