VLOOKUP Function: Complete Guide with Examples

VLOOKUP is one of the most widely used Excel functions of all time. Whether you are matching customer IDs to names, pulling prices from a product catalog, or combining data from two different sheets, VLOOKUP gets the job done with a single formula. This guide covers everything you need — syntax, real examples, common pitfalls, and when a different function is a better choice.

What Does VLOOKUP Do?

VLOOKUP stands for Vertical Lookup. It searches for a value in the first column of a range and returns a value from a specified column in the same row. Think of it as a precise search operation: you hand Excel a key, tell it where to look, and ask it to bring back a piece of information from the same record.

Common real-world uses include:

• Looking up a product price by SKU code
• Retrieving an employee's department by their ID number
• Matching invoice numbers to client names
• Grading test scores against a score table

VLOOKUP Syntax Explained

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Each argument plays a specific role:

Argument	Required?	What It Means
lookup_value	Yes	The value you want to find — a cell reference, number, or text string.
table_array	Yes	The range that contains your data. The lookup column must be the leftmost column of this range.
col_index_num	Yes	The column number (counted from the left of table_array) whose value you want to return.
range_lookup	No	FALSE (or 0) for an exact match; TRUE (or 1) for an approximate match. Defaults to TRUE if omitted.

Important: Always use FALSE for the fourth argument unless you are working with a sorted table and genuinely need an approximate match (such as a grade bracket or tax band lookup). Omitting it or using TRUE on unsorted data is a leading cause of incorrect results.

Step-by-Step Practical Example

Imagine you manage a small product catalog on Sheet1 and you want to pull prices into an order form on Sheet2. Here is how the data looks on Sheet1:

A — SKU	B — Product Name	C — Price
P001	Wireless Mouse	$29.99
P002	USB-C Hub	$49.99
P003	Mechanical Keyboard	$89.99
P004	Monitor Stand	$34.99

On Sheet2, column A contains the SKU entered by the user. To return the product name in column B of Sheet2, type:

=VLOOKUP(A2, Sheet1!$A$2:$C$5, 2, FALSE)

To return the price in column C of Sheet2, change the column index to 3:

=VLOOKUP(A2, Sheet1!$A$2:$C$5, 3, FALSE)

Why Use Absolute References?

Notice the dollar signs in Sheet1!$A$2:$C$5. These lock the range so that when you copy the formula down to other rows, the table_array does not shift. If you are unfamiliar with how cell references work, the article on Excel cell references explained: relative vs absolute covers this concept in full detail.

Using VLOOKUP with Approximate Match

Set the fourth argument to TRUE when your lookup table is sorted in ascending order and you want the closest match below the lookup value. A classic example is converting a raw score to a letter grade:

=VLOOKUP(B2, $E$2:$F$6, 2, TRUE)

E — Min Score	F — Grade
0	F
60	D
70	C
80	B
90	A

A score of 85 would match the 80 row and return "B". This only works correctly because the Min Score column is sorted from lowest to highest.

Troubleshooting Common VLOOKUP Errors

#N/A Error

This is the most frequent error. It means VLOOKUP could not find the lookup_value in the first column of your table. Check for:

• Extra spaces — use TRIM() on both the lookup value and the table data.
• Data type mismatch — a number stored as text will not match a true number. Select the column, go to Data → Text to Columns, and finish to convert.
• Typos — even one wrong character causes #N/A.
• Lookup value outside the first column — VLOOKUP can only search leftward-to-rightward; the lookup column must be the first column of your range.

To suppress the error while debugging, wrap the formula: =IFERROR(VLOOKUP(A2, $D$2:$F$10, 2, FALSE), "Not found")

#REF! Error

This appears when the col_index_num is larger than the number of columns in your table_array. For example, specifying column 5 when the range is only 3 columns wide. Count your columns and reduce the index accordingly.

#VALUE! Error

Usually caused by col_index_num being zero or a non-numeric value. The column index must be a positive integer of 1 or greater.

VLOOKUP Returns the Wrong Value

If you omitted the fourth argument (or set it to TRUE) but your table is not sorted, VLOOKUP may return an incorrect approximate match silently — with no error message at all. Always use FALSE for exact matches.

Nesting VLOOKUP with IF

You can combine VLOOKUP with logical functions for more nuanced results. For example, show a discount only if the lookup succeeds:

=IF(IFERROR(VLOOKUP(A2, $D$2:$F$10, 3, FALSE), "") = "", "No discount", VLOOKUP(A2, $D$2:$F$10, 3, FALSE))

To learn more about building logical tests inside formulas, see the complete guide on the IF function: logical tests and nested IFs.

VLOOKUP Across Multiple Sheets and Workbooks

You can reference data from another sheet by prefixing the range with the sheet name:

=VLOOKUP(A2, Catalog!$A$2:$C$100, 2, FALSE)

To reference a different workbook (while it is open):

=VLOOKUP(A2, [PriceList.xlsx]Sheet1!$A$2:$C$100, 2, FALSE)

If the workbook is closed, Excel will show the full file path automatically when you link to it while both files are open.

When to Use VLOOKUP vs. Alternatives

VLOOKUP Limitations

• Can only look left-to-right — the lookup column must always be on the far left of the range.
• Returns only one value per lookup.
• Inserting or deleting columns in the middle of the table_array breaks the hard-coded col_index_num.
• Case-insensitive by default.

INDEX MATCH: The Flexible Alternative

The INDEX MATCH combination removes the left-column restriction and is more robust when columns are added or rearranged. If you find yourself fighting VLOOKUP's limitations, the dedicated article on INDEX MATCH: the superior lookup method walks through the transition step by step.

XLOOKUP: The Modern Replacement

Available in Excel 365 and Excel 2021, XLOOKUP is simpler and more powerful:

=XLOOKUP(A2, Sheet1!$A$2:$A$5, Sheet1!$C$2:$C$5, "Not found")

It searches any direction, handles missing values natively, and does not require a numeric column index. If your version of Excel supports it, consider XLOOKUP for all new projects.

VLOOKUP in Real Business Scenarios

VLOOKUP pairs well with many other Excel workflows. For instance, a sales dashboard that tracks KPIs and performance often uses VLOOKUP to pull product names or rep territories from reference tables into summary reports. Similarly, building an invoice template with professional billing almost always involves a VLOOKUP that retrieves unit prices from a product list based on item codes entered by the user.

For teams working with large data sets, combining VLOOKUP with Pivot Tables is a productive workflow: use VLOOKUP to enrich raw data with category labels, then summarize in a Pivot Table.

Write VLOOKUP Formulas with Plain English

If you know what you need but cannot remember the exact syntax — for example, "look up the employee ID in column A of the HR sheet and return their salary from column D" — GPTExcel lets you describe the need in plain English and generates the correct VLOOKUP formula instantly, ready to paste into your spreadsheet.

Frequently Asked Questions

Why does my VLOOKUP return a result for some rows but #N/A for others?

The most likely cause is inconsistent data types or extra whitespace in certain cells. Run =TRIM(A2) on your lookup values and make sure all entries in the lookup column are stored as the same data type (all text or all numbers). You can also use =IFERROR(VLOOKUP(...), "Check data") to identify which rows fail without breaking the rest of your report.

Can VLOOKUP return values from multiple columns at once?

Not with a single formula in the traditional sense. You need a separate VLOOKUP for each column you want to return, changing only the col_index_num. Alternatively, XLOOKUP in Excel 365 can return an entire row of results with one formula by specifying a multi-column return array.

Does VLOOKUP work with duplicate values in the lookup column?

VLOOKUP always returns the value corresponding to the first match it finds, scanning from top to bottom. If your lookup column contains duplicates, subsequent matches are ignored. For scenarios involving duplicates, consider using a Pivot Table or helper columns to deduplicate before looking up.

Is VLOOKUP case-sensitive?

No. VLOOKUP treats uppercase and lowercase letters as identical. Searching for "apple" will match "Apple" or "APPLE". If you need a case-sensitive lookup, you must use an array formula combining EXACT() and INDEX/MATCH instead.