Lookup & reference › XLOOKUP82 / 144

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

EasyFreeXLOOKUP

XLOOKUP: find a price from a product code

Cashiers at Mr. Yusuf's hardware store in Jember often misread prices from a long list. Mr. Yusuf wants to type a product code in E2 and have the price appear in F2 automatically.

Task

In F2, show the price (column C) for the product code typed in E2, using XLOOKUP.

Cells to fillF2

Required functionsXLOOKUP

Expected result

Loading the expected result…

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

Hint 1
The basic shape: XLOOKUP(lookup_value, lookup_column, return_column).
Hint 2
The value to find is in E2, the codes are in A2:A6 and the prices are in C2:C6.
Hint 3
Pattern: =XLOOKUP(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

=XLOOKUP(E2,A2:A6,C2:C6)

Explanation

=XLOOKUP(E2,A2:A6,C2:C6) finds P03 in the code column and returns the price from the same row. Unlike VLOOKUP, you do not count column numbers, and the return column may sit to the left of the lookup column. If the code is missing, the result is #N/A; the fourth argument of XLOOKUP can supply a fallback value.

Starting worksheet: Sheet1
Row	A	B	C	E	F
1	Code	Product	Price	Code to find	Price
2	P01	Cement 40 kg	62,000	P03
3	P02	Sand per sack	18,000
4	P03	Wall paint 5 kg	135,000
5	P04	Nails 1 kg	24,000
6	P05	PVC pipe 3 m	47,500
7
8
9
10
11
12
13
14

Fill in the requested cell, then press Check answer.

Validation result