What VLOOKUP Does
VLOOKUP searches for a value in the first column of a range and returns a value from another column in the same row. Think of it as looking someone up in a phone book by name and reading their number. It is one of the most widely used functions in Google Sheets for pulling data from reference tables.
Syntax
=VLOOKUP(search_key, range, index, [is_sorted])
| Parameter | Description |
|---|---|
| search_key | The value to search for in the first column of the range. |
| range | The table range to search. The search key must be in the first column. |
| index | The column number (starting at 1) in the range from which to return a value. |
| is_sorted | FALSE for exact match (most common), TRUE for approximate match on sorted data. Defaults to TRUE. |
Basic Examples
Example 1: Look Up a Product Price
You have a price list in columns E-F and want to pull the price for a given product.
| E | F | |
|---|---|---|
| 1 | Product | Price |
| 2 | Laptop | 999 |
| 3 | Mouse | 25 |
| 4 | Monitor | 349 |
Formula in cell B2:
=VLOOKUP("Mouse", E2:F4, 2, FALSE)
Result: 25
Example 2: Pull Employee Department
| A | B | C (Formula) | C (Result) | |
|---|---|---|---|---|
| 1 | Emp ID | Name | Department | Department |
| 2 | 1042 | — | =VLOOKUP(A2, Staff!A:C, 3, FALSE) | Marketing |
This searches the Staff sheet for employee 1042 and returns the value from the 3rd column.
Advanced Examples
VLOOKUP with Wildcards
Find a customer whose name starts with "John":
=VLOOKUP("John*", A2:D100, 4, FALSE)
The asterisk acts as a wildcard, matching "John Smith", "Johnny B", etc. It returns the value from the 4th column for the first match.
Two-Way Lookup with VLOOKUP + MATCH
When your column position changes or you want a dynamic column, combine VLOOKUP with MATCH:
=VLOOKUP(A2, Products!A:F, MATCH("Margin", Products!1:1, 0), FALSE)
MATCH finds the column number for "Margin" in the header row, so the formula adapts even if columns are rearranged.
Common Mistakes
- Always use FALSE for exact match. The default (
TRUE) assumes sorted data and uses approximate matching, which gives wrong results on unsorted lists. Almost every use case calls forFALSE. - Search key must be in the first column. VLOOKUP can only search the leftmost column of your range. If the value you need is to the left of the lookup column, use INDEX/MATCH or XLOOKUP instead.
- Hardcoding the column index. Using a number like
3breaks if you insert a column. Use MATCH to calculate the index dynamically.
SheetAI Tip
Don't want to memorize this syntax? With SheetAI, just type what you need in plain English — like "look up the price for each product from the pricing table" — and the formula is generated for you. Install SheetAI to try it free.