← Google Sheets from Scratch: Formulas, QUERY, and Collaboration
Lesson
Lesson 1: VLOOKUP and the INDEX/MATCH combo
Find a value by its search key with VLOOKUP, and understand when you need the more flexible INDEX/MATCH combo instead.
How VLOOKUP works and where it falls short
How VLOOKUP works and where it falls short
VLOOKUP is the best-known lookup formula in spreadsheets. Syntax: =VLOOKUP(search_key, range, col_index, FALSE). It looks for the search key in the FIRST (leftmost) column of the range and then returns the value from the column with the given number. For example, =VLOOKUP("Apple", A2:C10, 2, FALSE) finds the row where column A says “Apple” and returns the value from column B.
The most important detail is the fourth argument (is_sorted). By default it is TRUE, which means an approximate match (the formula assumes the data is sorted). This is a common mistake: if you forget to write FALSE, the formula can return the wrong result. Always use FALSE for an exact match.
The main limitation of VLOOKUP: it can only look to the RIGHT of the lookup column. If the result you need is to the left of the search key, VLOOKUP can’t help. This is where the INDEX/MATCH combo comes in.
=INDEX(result_column, MATCH(search_key, lookup_column, 0)) works in two steps: MATCH finds the row number where the search key appears, and INDEX returns the value from the column you need at that row number. This combo is more flexible: the lookup and result columns can be in any order, and it doesn’t depend on column numbers within a range.
Lesson notes
How VLOOKUP works and where it falls short
VLOOKUP is the best-known lookup formula in spreadsheets. Syntax: =VLOOKUP(search_key, range, col_index, FALSE). It looks for the search key in the FIRST (leftmost) column of the range and then returns the value from the column with the given number. For example, =VLOOKUP("Apple", A2:C10, 2, FALSE) finds the row where column A says “Apple” and returns the value from column B.
The most important detail is the fourth argument (is_sorted). By default it is TRUE, which means an approximate match (the formula assumes the data is sorted). This is a common mistake: if you forget to write FALSE, the formula can return the wrong result. Always use FALSE for an exact match.
The main limitation of VLOOKUP: it can only look to the RIGHT of the lookup column. If the result you need is to the left of the search key, VLOOKUP can’t help. This is where the INDEX/MATCH combo comes in.
=INDEX(result_column, MATCH(search_key, lookup_column, 0)) works in two steps: MATCH finds the row number where the search key appears, and INDEX returns the value from the column you need at that row number. This combo is more flexible: the lookup and result columns can be in any order, and it doesn’t depend on column numbers within a range.