SheetsLessons
Lookup function

VLOOKUP function in Google Sheets

VLOOKUP searches down the first column of a table for a value and returns something from the same row: a name for an ID, a price for a product, a rate for a tier. It's the most searched-for function in spreadsheets.

Syntax

=VLOOKUP(search_key, range, index, [is_sorted])
search_key
The value to look for, typed in ("Webcam", 1305) or a cell reference.
range
The table to search. VLOOKUP only searches its first (left-most) column.
index
Which column of the range to return, counting the range's first column as 1.
is_sorted
Optional. FALSE for an exact match (what you nearly always want). Defaults to TRUE, an approximate match on sorted data.

Practice VLOOKUP

Task 1 of 4

Use VLOOKUP to find the price of the Marker Set. Answer in E2.

E2
fx

Tip: while typing a formula, click a cell or drag across cells to insert references. Press Enter to check, and Tab to accept a function suggestion.

VLOOKUP examples

Each example below runs against this sample sheet. The results are calculated by the same engine as the practice grid.

ABCDE
1Emp IDNameDeptEmailSalary
21042Maya PatelFinancemaya.patel@acme.co$72,000.00
31187Leo RossiSupportleo.rossi@acme.co$51,000.00
41305Ava KimSalesava.kim@acme.co$64,000.00
51411Omar AliMarketingomar.ali@acme.co$58,000.00
61520Hana TanakaSaleshana.tanaka@acme.co$69,000.00
Employee directory
FormulaResultWhat it shows
=VLOOKUP(1305, A2:E6, 2, FALSE)Ava KimName for employee 1305.
=VLOOKUP(1305, A2:E6, 5, FALSE)$64,000.00Same row, salary column.
=VLOOKUP(1300, A2:B6, 2, TRUE)Leo RossiApproximate match: the largest ID ≤ 1300.
=VLOOKUP(9999, A2:B6, 2, FALSE)#N/ANot in the list returns #N/A.

Tips and common mistakes

  • Always type FALSE as the 4th argument unless you deliberately want an approximate match. The TRUE default silently returns wrong answers on unsorted data.
  • VLOOKUP can't look to its left. If the value you want is left of the search column, use XLOOKUP or INDEX/MATCH.
  • index is a hard-coded number, so inserting a column inside the range shifts it. XLOOKUP avoids this.
  • Extra spaces cause #N/A even when values look identical. Clean the data with TRIM.

Related functions

XLOOKUPFinds a value in one range and returns the matching item from another.HLOOKUPSearches the first row of a range and returns a value from the same column.INDEXReturns the cell at a given row and column of a range.MATCHReturns the position of a value in a row or column.IFNAReturns a fallback only for #N/A errors.

Learning step by step? VLOOKUP is covered in Lesson 16: VLOOKUP Function of the free course.