Unit 7 of 12

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

Use XLOOKUP to find 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-103
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: =XLOOKUP(F2, IDs column, Price column)
Show answer
=XLOOKUP(F2,A2:A7,C2:C7) in G2. No column counting needed. Just point at what to search and what to return.
What can XLOOKUP do that VLOOKUP cannot?
- Return a column to the left of the search column
- Add numbers
- Format cells
- Sort data
Show answer
Return a column to the left of the search column. VLOOKUP always searches the first column and returns to the right.
You know the Item name in F2. Find its ID (to the left) in G2.
A B C D E F G
1 ID Item Price Stock Find item ID
2 P-101 Desk lamp 34.99 12 Keyboard
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: Search the Item column B, return the ID column A.
Show answer
=XLOOKUP(F2,B2:B7,A2:A7) in G2. Looking left is one of XLOOKUP's biggest wins.
Build: price lookup that shows "Not found" when missing
Show answer
=XLOOKUP(F2,A2:A7,C2:C7,"Not found"). The 4th argument is what to show when nothing matches.
What will G2 show?
A B C D E F G
1 ID Item Price Stock
2 P-101 Desk lamp 34.99 12 P-777
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 =XLOOKUP(F2,A2:A7,C2:C7,"Not found")
Show answer
Not found. The if_not_found argument replaces #N/A.

	A	B	C	D	F	G
1	ID	Item	Price	Stock	Find ID	Price
2	P-101	Desk lamp	34.99	12	P-103
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 item	ID
2	P-101	Desk lamp	34.99	12	Keyboard
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-777
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 3: INDEX and MATCH

What will C1 show?
A B C
1 Notebook
2 Pen
3 Stapler
4 Binder
C1 contains =MATCH("Pen",A1:A4,0)
Show answer
2. MATCH returns the position, not the value. Pen is 2nd.
What will C1 show?
A B C
1 10
2 20
3 30
4 40
C1 contains =INDEX(A1:A4,3)
Show answer
30. INDEX returns the item at position 3.
Combine INDEX and MATCH to find 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-106
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: =INDEX(prices, MATCH(F2, IDs, 0))
Show answer
=INDEX(C2:C7,MATCH(F2,A2:A7,0)) in G2. Works in every version of Excel, which is why analysts still love it.
Why use 0 as MATCH's last argument?
- It forces an exact match
- It starts counting at zero
- It returns 0 when missing
- It ignores blanks
Show answer
It forces an exact match. 0 means exact. 1 and -1 are for sorted approximate matches.
Build: which position is "Webcam" in B2:B7?
Show answer
=MATCH("Webcam",B2:B7,0). MATCH(what, where, 0).

	A	B	C
1	Notebook
2	Pen
3	Stapler
4	Binder