VLOOKUP in Google Sheets lets you search a table vertically and pull a matching value into a new column. This function is widely used for combining data from multiple sheets, cleaning lists, and enriching reports without manual copy-pasting.
Whether you are analyzing leads, managing inventory, or summarizing survey responses, mastering VLOOKUP reduces errors and saves time. The sections below walk through core use cases, exact syntax, common pitfalls, and frequently asked questions.
| Function | Purpose | Typical Use Case | Example Formula |
|---|---|---|---|
| VLOOKUP | Search vertically by a key and return a value from a specified column | Look up a product ID to retrieve price or supplier name | =VLOOKUP(A2, Products!A:C, 3, FALSE) |
| Exact Match Mode | Find only precise matches and return #N/A for no match | Validating unique customer codes in a master list | =VLOOKUP(D2, Data!A:E, 5, FALSE) |
| Approximate Match Mode | Find the largest value less than or equal to the lookup key, requires sorted data | Assigning tax brackets based on income ranges | =VLOOKUP(E2, Brackets!A:B, 2, TRUE) |
| Column Index Number | Which column to return, counted from the first column in the range | Returning the third column from the table range | =VLOOKUP(F2, A:D, 3, FALSE) |
Basic VLOOKUP Syntax and Parameters
Understanding the Arguments
The core syntax is =VLOOKUP(search_key, range, index, [sorted]). The search_key is the value you want to find, such as an email or product code. The range is the table where the search happens, including the lookup column and the columns you want to return. The index is a number that tells Google Sheets which column to pull from, counted from the first column in the range. The optional sorted argument is FALSE for exact matches or TRUE for approximate matches, with FALSE recommended for most lookups.
Always anchor your table range with $ signs when copying formulas down or across. For example, =VLOOKUP(A2, $B$2:$E$100, 4, FALSE) keeps the lookup table fixed while the search key moves down rows. This prevents shifted ranges and reduces troubleshooting time.
Common VLOOKUP Use Cases in Business
Merging Data from Multiple Sheets
Marketing and finance teams often split data by month or region into separate sheets. VLOOKUP can pull metrics from each sheet into a summary dashboard, ensuring a single source of truth. Use consistent key names and table structures to simplify these cross-sheet lookups and avoid broken references.
Enriching Leads and Customer Records
Sales teams can import a list of company names and use VLOOKUP to append industry, size, or lead score from a master database. This approach helps prioritize outreach and segment campaigns without manually updating every row in your CRM.
VLOOKUP Errors and How to Fix Them
Understanding #N/A and REF Issues
The most common errors are #N/A when no match is found and REF errors when a column index is outside the range. Double-check that the lookup key exists in the first column of the range and that the index does not exceed the number of columns in the table. Wrapping VLOOKUP with IFERROR can hide problems, so fix the source data whenever possible.
Sorting the source data is unnecessary for exact matches but required for approximate matches to work correctly. If your sorted flag is TRUE, ensure the first column in the range is in ascending order, or switch to FALSE to avoid misleading results.
Advanced VLOOKUP Techniques
Combining with Other Functions
Use COLUMN to generate the index number dynamically when pulling across adjacent columns. Wrap VLOOKUP inside IF or AND to add conditional logic, such as skipping blank search keys or validating multiple criteria. For more complex matching, consider INDEX MATCH or XLOOKUP when your platform supports them.
Optimizing Large Datasets
Limit the range to only the rows and columns you need, and avoid entire column references like B:E in busy sheets. Sorting the lookup column and using approximate match can speed up very large tables, but exact match with filtered data is often safer and more predictable.
Best Practices for Reliable VLOOKUP Workflows
- Use absolute ranges ($A$1:$D$100) to prevent accidental shifts when copying formulas.
- Prefer FALSE for the sorted argument unless you intentionally need approximate matches on sorted data.
- Validate lookup keys for duplicates, as VLOOKUP returns the first match it finds.
- Limit column index numbers to the actual width of your table to avoid REF errors.
- Consider INDEX MATCH or XLOOKUP for more flexible two-way lookups and leftward searches.
FAQ
Reader questions
Can VLOOKUP search to the left in Google Sheets?
No, VLOOKUP can only search in the first column of the selected range and return values from columns to the right. To look left, reorder your source table or use a combination of INDEX and MATCH.
What is the difference between using TRUE and FALSE for the sorted argument?
FALSE requires an exact match and returns #NOA if no equal value is found, while TRUE finds the closest match less than or equal to the lookup key, which requires the first column to be sorted in ascending order.
Why does my VLOOKUP return the wrong value when I copy it to another cell?
This usually happens when the table range is not anchored with dollar signs, causing the range to shift as the formula is filled. Fix it by using absolute references like $B$2:$F$50.
How can I make VLOOKUP case-sensitive in Google Sheets?
VLOOKUP is not case-sensitive by itself. To perform a case-sensitive lookup, pair EXACT with an array formula such as =INDEX(C:C, MATCH(TRUE, EXACT(A2, A:A), 0)) and confirm with Ctrl+Shift+Enter in legacy Google Sheets.