Price lookup
=VLOOKUP(A2, Products!A:D, 3, FALSE) returns price from third column of the product table.
What is VLOOKUP in Google Sheets?
VLOOKUP searches the first column of a range for a key and returns a value from a specified column to the right. It is still everywhere in legacy templates even as XLOOKUP offers more flexible replacements.
When to use it
Use VLOOKUP when maintaining existing models, teaching beginners, or when keys always sit in the leftmost column of a stable table.
When to skip it
Skip VLOOKUP for left lookups or when insert-column breaks hard-coded index numbers. XLOOKUP or INDEX MATCH may be safer.
How it works
-
1
Put the lookup key in the formula or cell reference.
-
2
Select table array with key column as the leftmost column of the range.
-
3
Set col_index_num counting from the left of table array, not the sheet.
-
4
Use FALSE or 0 for exact match in most business lookups.
-
5
Lock table array with $ when copying the formula across rows.
-
6
Wrap with IFERROR to show friendly text when SKU is missing.
Examples in Google Sheets
Employee dept
=VLOOKUP(B2, Staff!A:C, 2, FALSE) fetches department from ID in column A.
Grade band
=VLOOKUP(D2, Scale!A:B, 2, TRUE) approximate match on sorted scale for letter grade.
Build this without starting from a blank cell
Use a Better Sheets tool for VLOOKUP, then watch a walkthrough when you want the full pattern.
Better Sheets resources
Common mistakes
- Inserting a column in the table without updating col_index_num.
- TRUE approximate match on unsorted keys returning wrong rows.
- Key not in first column of range even though it looks left on sheet.
- Trailing spaces in keys breaking exact match.
- Table array missing absolute locks so copied formulas drift.
Frequently asked questions
- VLOOKUP vs XLOOKUP?
- XLOOKUP searches any direction and handles not-found text more cleanly.
- VLOOKUP left lookup?
- Not directly. Use INDEX MATCH or XLOOKUP instead.
- Why #N/A?
- Key not found with exact match, or approximate rules not met.
- VLOOKUP column index?
- Count columns within the table array starting at 1 for the leftmost column of the array.
- VLOOKUP multiple criteria?
- Helper column concatenating keys or use FILTER/XLOOKUP patterns.
- VLOOKUP case sensitive?
- Not by default. Exact match is case-insensitive in typical Sheets behavior.
- Migrate to XLOOKUP?
- Map table key and return columns to lookup_array and return_array with search_mode 0.
Related Tutorials
Watch how VLOOKUP works
Use Index Match when you want Vlookup
Find Column with Header to Bullet Proof your Vlookup
Related blog posts
Guides that explain VLOOKUP in more depth.
How To Calculate ROAS in Google Sheets
Showing you two ways to use sheets to calculate Return on ad Spend. ROAS. One way with formulas and one way with Apps Script. Learn more at Better Sheets: https://Bettersheets.co
Read post →How To Make That Viral Growing Plant Progress Chart
You might have seen a viral TikTok where there is a progress bar and a growing sort of garden of flowers or potted plant that's growing as you check off these task bars tasks. So I want to walk through what this formula, the combination is how it's really cool it
Read post →Five Frustrating Formulas and How to Conquer Them
Google Sheets formulas can be frustrating for a few reasons. First, you’re trying to remember what to use on the fly. Then you can't remember the name of a formula. If you do figure out the name, you can't remember the syntax. Then you don't know the proper way to write the data. FRUSTRATING!
Read post →Related glossary terms
XLOOKUP
XLOOKUP searches a lookup array for a key and returns a matching value from a return array. It handles left lookups, approximate match, and custom not-found text without INDEX/MATCH gymnastics.
Read guide →HLOOKUP
HLOOKUP is the horizontal cousin of VLOOKUP. It searches the first row of a range for a key and returns a value from a specified row below. Wide month grids and matrix headers occasionally still use it.
Read guide →INDEX
INDEX fetches a value from a range by row and column number. Combined with MATCH it powers flexible lookups. Alone it picks nth items from lists, dynamic headers, or matrix intersections without VLOOKUP column limits.
Read guide →MATCH
MATCH searches a range for a value and returns its relative position. Feeding INDEX or OFFSET with MATCH replaces brittle column index math. Sorted approximate match modes support tier tables and date brackets.
Read guide →Done reading about VLOOKUP?
Membership unlocks 636+ tutorials, unlimited generators, and every template. Practical lessons. Zero fluff.
Need this once
Jump to a free tool or a single tutorial for this topic.
Learning Sheets for real
Unlock the full library, generators, and templates with membership.