Unit 7 of 12 Lesson 1 free · Plus

Lookups

Find answers in another table.

Functions in this unit: VLOOKUP, XLOOKUP, MATCH, INDEX

Key ideas

VLOOKUP needs FALSE

=VLOOKUP(F2,A2:D7,3,FALSE)

Find F2 in the first column, return column 3. FALSE means exact match only.

XLOOKUP is simpler

=XLOOKUP(F2,A2:A7,C2:C7)

Where to look, what to return. It can look left and has a built-in not-found message.

INDEX + MATCH

=INDEX(C2:C7,MATCH(F2,A2:A7,0))

MATCH finds the position, INDEX returns what is at that position.

Lesson 1: VLOOKUP

What does FALSE mean as the last VLOOKUP argument?
- Exact match only
- Return FALSE if missing
- Search from the bottom
- Ignore case
Show answer
Exact match only. Without FALSE, VLOOKUP guesses the closest match, which causes silent wrong answers.
Look up the price of the ID in F2. Put it in G2.
A B C D E F G
1 ID Item Price Stock Find ID Price
2 P-101 Desk lamp 34.99 12 P-104
3 P-102 Monitor arm 89 4
4 P-103 USB hub 24.5 30
5 P-104 Webcam 59.95 0
6 P-105 Keyboard 74 18
7 P-106 Mouse pad 12.75 52
Hint: =VLOOKUP(F2,A2:D7,3,FALSE). Price is the 3rd column of the table.
Show answer
=VLOOKUP(F2,A2:D7,3,FALSE) in G2. Change F2 and the price updates. That is a live lookup.
Build: find the Item name for the ID in F2
Show answer
=VLOOKUP(F2,A2:D7,2,FALSE). Item is the 2nd column of A2:D7.
What will G2 show? The ID does not exist.
A B C D E F G
1 ID Item Price Stock
2 P-101 Desk lamp 34.99 12 P-999
3 P-102 Monitor arm 89 4
4 P-103 USB hub 24.5 30
5 P-104 Webcam 59.95 0
6 P-105 Keyboard 74 18
7 P-106 Mouse pad 12.75 52
G2 contains =VLOOKUP(F2,A2:D7,3,FALSE)
Show answer
#N/A. #N/A means "not available": the value was not found.
How many are in stock for the ID in F2? Put it in G2.
A B C D E F G
1 ID Item Price Stock Find ID Stock
2 P-101 Desk lamp 34.99 12 P-105
3 P-102 Monitor arm 89 4
4 P-103 USB hub 24.5 30
5 P-104 Webcam 59.95 0
6 P-105 Keyboard 74 18
7 P-106 Mouse pad 12.75 52
Hint: Stock is column 4.
Show answer
=VLOOKUP(F2,A2:D7,4,FALSE) in G2. Same formula, different column number.

	A	B	C	D	F	G
1	ID	Item	Price	Stock	Find ID	Price
2	P-101	Desk lamp	34.99	12	P-104
3	P-102	Monitor arm	89	4
4	P-103	USB hub	24.5	30
5	P-104	Webcam	59.95	0
6	P-105	Keyboard	74	18
7	P-106	Mouse pad	12.75	52

	A	B	C	D	F
1	ID	Item	Price	Stock
2	P-101	Desk lamp	34.99	12	P-999
3	P-102	Monitor arm	89	4
4	P-103	USB hub	24.5	30
5	P-104	Webcam	59.95	0
6	P-105	Keyboard	74	18
7	P-106	Mouse pad	12.75	52

	A	B	C	D	F	G
1	ID	Item	Price	Stock	Find ID	Stock
2	P-101	Desk lamp	34.99	12	P-105
3	P-102	Monitor arm	89	4
4	P-103	USB hub	24.5	30
5	P-104	Webcam	59.95	0
6	P-105	Keyboard	74	18
7	P-106	Mouse pad	12.75	52

Lesson 2: XLOOKUP

Lesson 2 is part of Cellwise Plus. Lesson 1 above is free to read here and to practice in the app.

See what Plus includes

Lesson 3: INDEX and MATCH

Lesson 3 is part of Cellwise Plus. Lesson 1 above is free to read here and to practice in the app.