If you’ve ever needed to find a product price by ID in a spreadsheet, you know the drill—scroll, search, hope. VLOOKUP automates that hunt in a single formula. With a step-by-step example, you’ll learn how to use it today, from syntax to real results.
Most used Excel function: among lookup functions · Official page rank: Top 1 on Google for ‘VLOOKUP’ · Users per month (est.): over 5 million unique searches
Quick snapshot
- VLOOKUP stands for Vertical Lookup and searches the first column (Microsoft Support)
- Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) (Microsoft Support)
- Exact number of Excel users worldwide
- How Microsoft plans to phase out VLOOKUP in future versions
- First appeared: Microsoft Excel in the 1990s (Microsoft Support)
- Still supported in Excel 365 (Microsoft Support)
- XLOOKUP can search both left and right (Microsoft Support)
- INDEX-MATCH alternative is more flexible (Microsoft Support)
VLOOKUP is the most-taught Excel lookup function, but XLOOKUP is already replacing it. For anyone building new spreadsheets, learning VLOOKUP is still the fastest path to understanding how lookup functions work.
Five key facts about VLOOKUP.
| Attribute | Value |
|---|---|
| Function Name | VLOOKUP |
| Full Name | Vertical Lookup |
| First Appeared | Microsoft Excel 1990s |
| Latest Version | Excel 365 (still supported) |
| Search Direction | Left to right only |
| Default Match Type | Approximate |
| Alternative Function | XLOOKUP, INDEX-MATCH |
| Case Sensitivity | Not case-sensitive |
| Performance on Large Data | Moderate |
What does VLOOKUP in Excel do?
How VLOOKUP searches vertically
- VLOOKUP stands for Vertical Lookup – it searches down the first column of a table range (Microsoft Support)
- When it finds a match in the first column, it returns a value from the same row in a column you specify
- The lookup column must be the leftmost column of your table array (W3Schools)
Think of it like a phonebook: you search by name (first column) and retrieve the number (any column to the right). The function cannot look left—once your data is set up, the lookup value must be in the first column of the selected range.
Common use cases for VLOOKUP
- Look up product prices by ID (PerfectXL Academy)
- Find employee names from employee IDs
- Combine customer data from different worksheets (Microsoft Support)
The implication: VLOOKUP is built for simple left-to-right lookups. If your data isn’t structured with the lookup value in column A, you’ll need to reorganize first.
How do I use VLOOKUP step by step?
- Step 1: Set up your data table
- Place your lookup values (e.g., product IDs) in the first column of your table
- Example: column A = Product ID, column B = Product Name, column C = Price
- Step 2: Write the VLOOKUP formula
- Select the output cell (e.g., cell F3 where you want the price)
- Type
=VLOOKUP(then click the cell containing the product ID you are searching for (Spreadsheeto) - Select the table array range (e.g., A:C) covering both the lookup column and the return column
- Enter the column index number (1 for first column, 2 for second, etc.)
- Enter FALSE for an exact match – always use FALSE unless you need approximate matching (Microsoft Support)
- Complete the formula:
=VLOOKUP(E3, A:C, 3, FALSE)
- Step 3: Copy the formula down
- Drag the fill handle from the first cell down to apply VLOOKUP to all product IDs
- Excel returns the corresponding price for each ID or #N/A if not found
The catch: VLOOKUP’s single-formula simplicity is its power. Copy it down and the table array reference will adjust unless you lock it with absolute references ($A:$C).
How to VLOOKUP with example?
Example: Look up product price by ID
Suppose you have this data in cells A2:C10:
| Product ID | Product Name | Price |
|---|---|---|
| 101 | Apple | 0.50 |
| 102 | Banana | 0.30 |
| 103 | Kiwi | 0.70 |
To find the price of Kiwi (ID 103), enter: =VLOOKUP(103, A2:C10, 3, FALSE) – returns 0.70 (PerfectXL Academy). The three arguments are clear: search for 103 in column A, look in the range A2:C10, and return the value from the third column (Price).
Example: Combine VLOOKUP with other functions
- Wrap VLOOKUP in IFNA to handle missing IDs:
=IFNA(VLOOKUP(E3, A:C, 3, FALSE), "Not found") - Use with MATCH to make column index dynamic
The pattern: VLOOKUP alone gives you the price; adding IFNA turns errors into friendly messages—a small upgrade that saves confusion for end users.
Beginners who master this single example are ready for 90% of real-world lookup tasks. The other 10%—looking left or handling multiple criteria—requires a more advanced function like XLOOKUP or INDEX-MATCH.
Always use FALSE for exact match to avoid false positives. The TRUE (approximate) mode is used only for grade boundaries or tax brackets where closest match is correct (W3Schools).
What are the benefits of VLOOKUP in Excel?
Speed in large datasets
- Manual searching through hundreds of rows takes minutes; VLOOKUP returns results instantly
- Works across multiple worksheets by referencing the sheet name (Microsoft Support)
Ease of use for beginners
- Widely taught in schools and online tutorials
- No programming required – just one formula with four intuitive arguments
No programming required
- VLOOKUP is built into Excel and Google Sheets with identical syntax
- Even users with zero coding experience can start using it in minutes
Why this matters: VLOOKUP’s low barrier to entry is why it remains the number one lookup function for entry-level spreadsheet work. You don’t need to be a data analyst to pull a price from a list.
How to explain VLOOKUP to dummies?
The phonebook analogy
- VLOOKUP is like looking up a person’s name in a phonebook to find their phone number
- You search the first column (names) and get a value from the same row (number) (Reddit user r/excel)
Parts of the formula explained
lookup_value– what you are searching for (e.g., product ID)table_array– the entire block of data (e.g., A:C)col_index_num– which column to return from (1,2,3…)range_lookup– FALSE for exact match, TRUE for approximate
The implication: once you see VLOOKUP as a simple retrieval tool, the syntax becomes a natural language sentence: “Find this value in the first column of that range, then give me what’s in the third column.”
Is there a better way than VLOOKUP?
Two modern alternatives, one key difference: XLOOKUP can search left and right; VLOOKUP cannot. Here’s a comparison with INDEX-MATCH.
| Feature | VLOOKUP | XLOOKUP | INDEX-MATCH |
|---|---|---|---|
| Search direction | Left to right only | Any direction | Any direction |
| Default exact match | No (default approximate) | Yes (default exact) | Requires exact mode in MATCH |
| Handles inserted columns | Breaks (col_index_num shifts) | Uses range references | Does not break |
| Available in | Excel 2000 – Excel 365 | Excel 2021 / 365 only | All versions |
Three entries, one pattern: VLOOKUP remains the most compatible (works in all versions), but XLOOKUP is more robust. For new workbooks, Microsoft Support recommends XLOOKUP when available.
What’s unclear?
Confirmed facts
- VLOOKUP searches vertically in the first column (Microsoft Support)
- Syntax is =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- Returns #N/A if not found
What’s unclear
- Exact number of Excel users globally
- Whether Microsoft will deprecate VLOOKUP in future releases
“VLOOKUP is ‘I need to locate a value in a list, then retrieve a value from a later column in that same row’”
– Reddit user r/excel
“Use VLOOKUP when you need to find things in a table or a range by row.”
– Microsoft Support
Upsides
- Speed in large datasets
- Ease of use for beginners
- No programming required
- Works across multiple worksheets
Downsides
- Only searches left to right
- Breaks when columns are inserted
- Requires manual exact match setting
- Not suitable for multiple criteria
For beginners building their first spreadsheet, the choice is clear: master VLOOKUP first because it teaches the core logic of lookups, then upgrade to XLOOKUP when your data demands more flexibility. Without VLOOKUP under your belt, the alternatives will feel abstract. With it, you can transition in minutes.
For a step-by-step walkthrough in another language, you can refer to this detailed Vietnamese guide on using VLOOKUP in Excel.
Frequently asked questions
Can VLOOKUP return multiple values?
No, VLOOKUP returns only the first match. For multiple matches, use INDEX-MATCH or FILTER.
What does #N/A mean in VLOOKUP?
#N/A means the lookup value was not found in the first column. Check for typos or exact match setting.
Can VLOOKUP work across different Excel files?
Yes, by including the workbook reference in the table_array, e.g., [Sales.xlsx]Sheet1!$A:$C.
How do I fix #REF! error in VLOOKUP?
#REF! error occurs when col_index_num exceeds the number of columns in the table_array. Reduce it.
Does VLOOKUP work in Google Sheets?
Yes, Google Sheets supports VLOOKUP with identical syntax.
What is the difference between VLOOKUP and HLOOKUP?
VLOOKUP searches vertically down the first column; HLOOKUP searches horizontally across the first row.
Can I use VLOOKUP with wildcards?
Yes, use * or ? in the lookup_value when range_lookup is FALSE.
Related reading