Excel VLOOKUP Function (Vertical Lookup) – Examples & Practice
Practice the Excel VLOOKUP function online with an interactive grid, instant feedback, and clear formula help.
Instruction
Use VLOOKUP to find a product price based on product code.
Formula Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- lookup_value: The value to find in the first column of table_array.
- table_array: The range that contains the lookup column and return columns.
- col_index_num: Column number within table_array (1 = first column of the range).
- [range_lookup]: FALSE for exact match; TRUE or omitted for approximate match on sorted data.
What it does
VLOOKUP searches the first column of a table for a value, then returns a cell from the same row in another column to the right. It is one of the most common ways to join spreadsheet data before modern dynamic functions.
Excel VLOOKUP Function Examples
Exact match price
=VLOOKUP("P-102", A2:C6, 3, FALSE)
Finds P-102 in the first column of A2:C6 and returns the value from the 3rd column (Price).
Approximate match on sorted numbers
=VLOOKUP(E1, A2:B20, 2, TRUE)
Returns the largest row where column A is less than or equal to E1 when A is sorted ascending—common for tax or commission brackets.
Wildcard partial text
=VLOOKUP("Mouse*", A2:C6, 2, FALSE)
Finds the first product name starting with “Mouse” when FALSE is used with wildcards (where your data supports it).
product-catalog.xlsx
| A | B | C | |
|---|---|---|---|
| 1 | Code | Name | Price |
| 2 | P-101 | Mouse | 19 |
| 3 | P-102 | Keyboard | 45 |
| 4 | P-103 | Monitor | 199 |
| 5 | P-104 | USB Hub | 29 |
| 6 | P-105 | Headset | 59 |
| 7 | Output |
Input Formula
Need Help?
Tips
- Keep lookup keys clean with **TRIM** when data comes from imports.
- Freeze table_array with $ when copying formulas.
- Prefer **XLOOKUP** on newer Excel if you need default “not found” text or left lookups.
VLOOKUP Function Use Cases
- Pull product price, name, or category from a catalog table
- Enrich transaction exports with master data
- Build simple dashboards that read from a reference sheet
- Validate that a code exists in an approved list
- Teach lookup fundamentals before XLOOKUP
Common mistakes - VLOOKUP function not working
- Forgetting
FALSEwhen you need an exact match - Wrong
col_index_numafter inserting or deleting columns - Including the header row inside
table_arrayinconsistently - Leading or trailing spaces in keys
- Using a whole-column reference and slowing the workbook
FAQ
When should I use FALSE for range_lookup?
Use FALSE for an exact match on unsorted keys. It is the safest default for product codes, IDs, and text keys.
Why do I get #N/A?
The lookup value is missing, there are extra spaces, numbers stored as text, or the key is not in the first column of the table_array.
Can VLOOKUP look to the left?
No. The return column must be to the right of the lookup column. Use XLOOKUP or INDEX/MATCH for leftward results.
What is col_index_num?
It is the column number within table_array, counting from the left edge of that range as column 1.
Is VLOOKUP case-sensitive?
No. Matches are case-insensitive for text in standard Excel behavior.
Comparison
| Function | Direction | Notes |
|---|---|---|
| VLOOKUP | Left-to-right only | Classic, widely known |
| HLOOKUP | Top-to-bottom | Row-based keys |
| XLOOKUP | Any direction | Modern replacement |
Example
=VLOOKUP("P-102", A2:C6, 3, FALSE)=XLOOKUP("P-102", A2:A6, C2:C6, "Not found")
Advanced examples
VLOOKUP + IFERROR
=IFERROR(VLOOKUP("P-999", A2:C6, 3, FALSE), "Missing")
Returns a friendly label when the key is not found.
Two-way lookup with MATCH
Use MATCH to compute col_index_num when the return column is chosen dynamically (advanced pattern; see INDEX MATCH).
Related functions
XLOOKUP is the modern default on supported builds—fewer column index mistakes and richer match modes.
Horizontal tables still need HLOOKUP occasionally; pair any lookup with IFERROR on shared sheets, and study INDEX MATCH when return columns are not fixed.