Lookup › INDEX, MATCH38 / 67

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

MediumFreeINDEXMATCH

INDEX + MATCH: find product names from SKUs

Dimas runs the warehouse of an online batik shop in Pekalongan. The stock list keeps the SKU code in the rightmost column, but the packing team only receives SKUs from orders and needs the product names.

Task

Fill F2:F5 with the product name for the SKU in column E. Because the SKU column (C) sits to the right of the name column (A), use INDEX + MATCH instead of VLOOKUP.

Cells to fillF2:F5

Required functionsINDEXMATCH

Expected result

Loading the expected result…

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

Hint 1
MATCH finds the position of the SKU in column C, then INDEX returns the content of column A at that same position.
Hint 2
The pattern is INDEX(result_column, MATCH(key, key_column, 0)).
Hint 3
Result column $A$2:$A$8, key column $C$2:$C$8, and the key is E2.

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

F2:F5

=INDEX($A$2:$A$8,MATCH(E2,$C$2:$C$8,0))

Write it in F2, then fill down to F5.

Explanation

=INDEX($A$2:$A$8,MATCH(E2,$C$2:$C$8,0)): MATCH finds DST-401 at position 5, then INDEX returns the 5th item of the name column, Batik house dress. Because the key column and the result column are written separately, you can look left or right, and the formula does not break when a new column is inserted. Make sure both ranges have the same height (rows 2 to 8).

Starting worksheet: Sheet1
Row	A	B	C	E	F
1	Product name	Stock	SKU	Ordered SKU	Product name
2	Parang batik shirt	14	BTK-101	DST-401
3	Mega mendung batik shirt	9	BTK-102	BTK-102
4	Kawung batik blouse	21	BTK-201	MSK-601
5	Hand-drawn batik cloth 2 m	5	KTN-301	KTN-301
6	Batik house dress	30	DST-401
7	Batik sarong	12	SRG-501
8	Batik face mask	80	MSK-601
9
10
11
12
13
14

Fill in the requested cell, then press Check answer.

Validation result