Lookup & reference › INDEX, MATCH87 / 144

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

MediumFreeINDEXMATCH

INDEX + MATCH: return a code from a column to the left

Warehouse staff at a pharmacy in Pontianak know medicine names by heart, not their SKU codes. In the master data, the SKU code is in column A and the name is in column B, so VLOOKUP cannot return the code that sits to the left of the name.

Task

Fill G2:G4 with the SKU code (column A) for each medicine name in F2:F4 by looking the name up in column B. Combine INDEX and MATCH.

Cells to fillG2:G4

Required functionsINDEXMATCH

Expected result

Loading the expected result…

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

Hint 1
MATCH(name, $B$2:$B$9, 0) returns the position of that name in the list.
Hint 2
INDEX(return_column, position) then takes the item at that same position in the return column. Here the return column is the code column, A2:A9.
Hint 3
Pattern: =INDEX($A$2:$A$9, MATCH(…, $B$2:$B$9, 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

G2:G4

=INDEX($A$2:$A$9,MATCH(F2,$B$2:$B$9,0))

Write it in G2, then fill down to G4.

Explanation

=INDEX($A$2:$A$9,MATCH(F2,$B$2:$B$9,0)) works in two steps: MATCH finds the position of the medicine name in column B, then INDEX returns the code at the same position in column A. Because the lookup and return columns are written separately, it can look left or right, and the formula does not break when a new column is inserted. Make sure both ranges start and end on the same rows, or the results shift.

Starting worksheet: Sheet1
Row	A	B	C	D	F	G
1	SKU	Medicine	Stock	Price	Medicine	SKU
2	OBT-101	Paracetamol 500 mg	240	8,000	Cetirizine 10 mg
3	OBT-102	Amoxicillin 500 mg	120	15,000	Cajuput oil 60 ml
4	OBT-103	Vitamin C 1000 mg	300	12,000	Paracetamol 500 mg
5	OBT-104	Ibuprofen 400 mg	90	11,000
6	OBT-105	Cetirizine 10 mg	75	9,500
7	OBT-106	ORS sachet	400	2,500
8	OBT-107	Skin ointment 15 g	60	18,000
9	OBT-108	Cajuput oil 60 ml	110	24,000
10
11
12
13
14

Fill in the requested cell, then press Check answer.

Validation result