Lookups & matching

Intermediate Google Sheets

Richie Cotton

Data Evangelist at DataCamp

	A	B	C	D
1	id	nspec
2	Ant	10k
3	Fly	125k
4	Bee	20k
5
6	id	size	command	nspec
7	Ant	25	=VLOOKUP(A7, $A$2:$B$4, 2, FALSE)	10k
8	Bee	40	=VLOOKUP(A8, $A$2:$B$4, 2, FALSE)	20k
9	Moth	300	=VLOOKUP(A9, $A$2:$B$4, 2, FALSE)	#N/A

Programmatically sorting data

=SORT($A$2:$C$5, 2, FALSE)

_ _

	A	B	C
1	id	nspec	size
2	Moth	250000	300
3	Fly	125000	70
4	Bee	20000	40
5	Ant	10000	25

Matching values

	A	B	C
1	id	nspec	size
2	Moth	250000	300
3	Fly	125000	70
4	Bee	20000	40
5	Ant	10000	25

_ _

=MATCH(100000, $B$2:$B$5, -1) 

2

Summary

• VLOOKUP() preforms left joins on two datasets.
• SORT() programmatically sorts data.
• MATCH() finds positions in sorted data where a value would occur.

Let's practice!

Intermediate Google Sheets