VLOOKUP - Search a table's first column
Retrieve a product price with an exact match and understand the sorted-table rule behind Excel's approximate-match default.
By the end, you can
- Count return columns within a table.
- Request exact matching explicitly.
- Use approximate matching only with suitable sorted boundaries.
Before you start: Read table ranges and fixed references. IFERROR can guard a missing price.
In this lesson
Use the left-most table column as the key
VLOOKUP finds a key down the first column of a specified table and returns a field to its right from the same row. An order desk can retrieve a catalog price from a product code. The first column means the left-most column of table_array, which does not have to be worksheet column A.
Syntax and arguments
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])The column number is relative to the table. In Excel, an omitted final argument means approximate matching, so use FALSE for identifiers.
| Argument | Purpose | Example |
|---|---|---|
lookup_valueRequired | The key to search in the table's first column. | D2 |
table_arrayRequired | A rectangular range including the key and return columns. | A2:C4 |
col_index_numRequired | The return column number, starting at 1 within the table. | 3 |
range_lookupOptional | FALSE/0 exact; TRUE/1 or omitted approximate. | FALSE |
A product price quotation
₹210| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Code | Product | Unit price | Order code | Price or message |
| 2 | P101 | Paper | ₹125 | P103 | ₹210 |
| 3 | P102 | Pens | ₹80 | P999 | Not listed |
| 4 | P103 | Ink | ₹210 | P101 | ₹125 |
=IFERROR(VLOOKUP(D2,$A$2:$C$4,3,FALSE),"Not listed")Return a label or calculate a line value
=VLOOKUP("P102",A2:C4,2,FALSE)The second table column contains Pens.
=VLOOKUP("P101",A2:C4,3,FALSE)*2The exact ₹125 unit price is multiplied by quantity 2.
A sorted lower-bound table
0| A | B | |
|---|---|---|
| 1 | Minimum order INR | Rate |
| 2 | 0 | 0% |
| 3 | 5000 | 5% |
| 4 | 10000 | 10% |
=VLOOKUP(7500,A2:B4,2,TRUE)Use the largest boundary not exceeding 7,500: 5,000, with a 5% rate.
Approximate matching requires ascending keys. A request below the lowest boundary returns #N/A. Omitting range_lookup in real Excel selects this approximate behavior, even when the key looks like a product code.
Limits, compatibility, and mistakes
- Exact matching works without sorting and ordinarily returns the first matching row. Duplicates need a business rule; VLOOKUP does not add all matching prices.
- It cannot return a column to the left of its table's key column. Use XLOOKUP or INDEX with MATCH instead.
- A column index outside the table gives #REF!; a missing exact key gives #N/A. A broad IFERROR also hides other errors, so diagnose unexpected labels.
- Text matching is case-insensitive; numeric and text-formatted keys can differ. VLOOKUP is supported in older and current Excel versions.
Check your understanding
Choose an answer for instant feedback. You can try again; answers stay only on this page.