Lookup › XLOOKUP36 / 67

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

EasyFreeXLOOKUP

XLOOKUP: shipping rates with a fallback text

Ayu sells tempeh chips from Malang via WhatsApp. She has a shipping-rate-per-kg table for the cities she ships to most often, but sometimes a buyer comes from a city that is not in the table yet.

Task

Fill C2:C6 with the shipping rate per kg for the city in column B, taken from the table E2:F7 using XLOOKUP. If the city is not in the table, show the text Check manually.

Cells to fillC2:C6

Required functionsXLOOKUP

Expected result

Loading the expected result…

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

Hint 1
XLOOKUP separates the column to search and the column to return: XLOOKUP(key, search_column, result_column, if_not_found).
Hint 2
The 4th argument is the text shown when the key is not found: "Check manually".
Hint 3
Search column $E$2:$E$7, result column $F$2:$F$7, and the key is B2.

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

C2:C6

=XLOOKUP(B2,$E$2:$E$7,$F$2:$F$7,"Check manually")

Write it in C2, then fill down to C6.

Explanation

=XLOOKUP(B2,$E$2:$E$7,$F$2:$F$7,"Check manually") searches for the city in column E and returns the rate from column F. Makassar and Pontianak are not in the table, so you get the text from the 4th argument instead of #N/A. Without that argument you would need to wrap the formula in IFNA. XLOOKUP is available in Google Sheets and defaults to an exact match, so there is no FALSE to remember as in VLOOKUP.

Starting worksheet: Sheet1
Row	A	B	C	E	F
1	Buyer	City	Shipping/kg	City	Shipping/kg
2	Fajar	Surabaya		Jakarta	18,000
3	Lestari	Makassar		Bandung	16,000
4	Dimas	Bandung		Surabaya	9,000
5	Nadia	Denpasar		Yogyakarta	14,000
6	Joko	Pontianak		Semarang	13,000
7				Denpasar	22,000
8
9
10
11
12
13
14

Fill in the requested cell, then press Check answer.

Validation result