VLOOKUP searches for a product code, customer ID, or another key in the first column of a table and returns a value from a column to its right. A reliable business-data formula usually uses FALSE for an exact match.
What does VLOOKUP do?
Suppose A2 contains a product code, F contains the master list of codes, and H contains prices. VLOOKUP can locate A2 in column F and return the price from the third column of the selected range.
Its syntax is VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). The lookup column must be the first column in table_array.
Exact-match VLOOKUP example
Lock the master-table range with dollar signs so it does not move when the formula is filled down. Use 3 because H is the third column in the F:H range.
Enter the formula in the first result row, copy it down, and verify several known records before relying on the full output.
How to do it
- Select the cell containing the lookup key.
- Choose a table range whose first column contains that key.
- Enter the return-column number and use FALSE for an exact match.
=VLOOKUP(A2,$F$2:$H$100,3,FALSE)Follow a VLOOKUP result in sample data
Suppose A2 contains P-102. VLOOKUP finds P-102 in column F of the source table and returns 245 from column H on the same row. Inside the F:H range, F is column 1, G is 2, and H is 3, which explains the column index in the formula.
| F · Code | G · Product | H · Price |
|---|---|---|
| P-101 | Pen | 50 |
| P-102 | Notebook | 245 |
| P-103 | Bag | 400 |
How do you use VLOOKUP across two sheets?
If A2 on your main sheet holds the product code and the Products sheet holds code, name, and price in A:C, use the formula below. Column A must be the first column of the selected lookup range. Single quotes around the sheet name also handle names with spaces.
Keep the dollar signs so the source range stays fixed when you fill the formula down. If the Products sheet contains the same code more than once, VLOOKUP returns the first matching value, not a list of every match.
=VLOOKUP(A2,'Products'!$A$2:$C$100,3,FALSE)How to prevent #N/A and wrong matches
#N/A often means the key is missing, one side stores numbers as text, or a value contains extra spaces. Clean the key columns and make their data types consistent.
VLOOKUP cannot return a value from a column to the left of the lookup column. Use XLOOKUP or INDEX and MATCH for that layout. Your Excel region may use semicolons instead of commas.
Frequently asked questions
How do I use VLOOKUP in Excel?
Enter the lookup value, a range whose first column contains that key, the return column's number within that range, and FALSE for an exact match: =VLOOKUP(A2,$F$2:$H$100,3,FALSE).
Why does VLOOKUP return #N/A?
The key may be absent, stored with a different data type, or contain extra spaces. Check the key columns and exact-match setting.
What does the 3 mean in a VLOOKUP formula?
In a selected F:H range, F is column 1, G is 2, and H is 3. The number is relative to the range, not the whole worksheet.
What is the difference between FALSE and TRUE?
FALSE requests an exact match. TRUE requests an approximate match and expects the first source column to be sorted; omitting the argument defaults to approximate. Use FALSE for product codes and IDs.
Can VLOOKUP look at another worksheet?
Yes. Include the worksheet name in the table range, for example =VLOOKUP(A2,'Products'!$A$2:$C$100,3,FALSE). The key must be in that range's first column.
What if a lookup key appears more than once?
VLOOKUP returns the value from the first matching row. Listing every match or using multiple criteria requires another approach.
Can VLOOKUP return a value from the left?
No. The return column must be to the right of the key within the selected range. XLOOKUP or INDEX with MATCH can handle a left-side return.
Does SheetCommand insert VLOOKUP formulas?
No. It can exactly match a key on the first sheet against a key on another sheet and add selected values as a new column. The output contains values, not VLOOKUP formulas; unmatched rows stay blank.
Sources and further reading
SheetCommand
Match records between two workbook sheets
Upload a workbook, then name the main key, source sheet, source key, and return column: VLOOKUP 'Product Code' with 'Products' sheet, match 'Code', bring 'Price'. Preview the results before downloading.