When working with large spreadsheets containing thousands of rows, manually scrolling to find specific information is inefficient. If you have an inventory ID number and need to know the price associated with that item, you need a way to automatically search the database and extract the answer.
The solution is the VLOOKUP function. VLOOKUP stands for “Vertical Lookup.” It tells Google Sheets to search vertically down a specific column for a search term, and when it finds a match, to look horizontally across that same row and retrieve data from a different column.
The Anatomy of a VLOOKUP Formula
A VLOOKUP formula looks intimidating, but it is actually just answering four simple questions:
=VLOOKUP(search_key, range, index, is_sorted)- Search Key: What exactly are you looking for? (e.g., an employee ID number).
- Range: Where should Google Sheets look for it? (e.g., the massive database table on another sheet).
- Index: Once it finds the employee ID, which column contains the answer you want? (e.g., the 3rd column, which contains their salary).
- Is Sorted: Do you want an exact match, or an approximate match?
How to Write the Formula
Imagine you have a small table of product prices. Column A contains Product IDs, Column B contains Product Names, and Column C contains the Price. You want to type a Product ID into cell E2, and have the Price automatically appear in cell F2.
- Click on cell F2 (this is where the answer will appear).
- Type
=VLOOKUP(to begin the formula. - Select the Search Key: Click on cell E2. (This tells Google Sheets to search for whatever number you type into E2). Type a comma.
- Select the Range: Click and drag to highlight your entire database table (e.g., A2:C100). Crucially, the column containing your Search Key MUST be the first (leftmost) column in your highlighted range. Type a comma.
- Select the Index: You highlighted Columns A, B, and C. You want the price, which is in Column C. Column C is the 3rd column in your highlighted range. Type the number
3. Type a comma. - Select Is Sorted: Type
FALSE. (Setting this to FALSE forces Google Sheets to find an exact match for your Product ID. If you set it to TRUE, it might guess and return the wrong product’s price). - Close the parentheses by typing
)and press Enter.
Your finished formula should look like this:
=VLOOKUP(E2, A2:C100, 3, FALSE)Understanding VLOOKUP Errors
If you write a VLOOKUP formula and receive an error instead of an answer, it is usually caused by one of two common mistakes:
- #N/A Error: This means Google Sheets searched the first column of your range but could not find an exact match for your search key. Double-check your spelling, or ensure there are no hidden spaces after the word in your database.
- #REF! Error: This means your Index number is too high. If you only highlighted columns A through C (a total of 3 columns), but you set your index to 4, Google Sheets cannot return an answer because column 4 is outside the range you highlighted.
The Limitations of VLOOKUP
VLOOKUP is a legacy function with one major flaw: it can only search from left to right. The column containing your search key must always be on the far left of your range, and it can only retrieve data from columns to its right.
If you need to search a column in the middle of your spreadsheet and retrieve data from a column to its left, VLOOKUP will fail. In those situations, you must use modern alternatives like the XLOOKUP function or an INDEX/MATCH combination.