VLOOKUP in Excel: Formula, Syntax and Examples

By Dr. Zubair Khalid, DVM, MS, PhD · 2026-10-03

The VLOOKUP formula in Excel finds a value in the first column of a table and returns a value from another column in the same row. You give it four pieces of information: what to look up, where to look, which column to return, and whether you want an exact or approximate match. This article covers the syntax, a worked example, the errors you will hit, and when another function is a better choice.

Quick Answer

• The full form is =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup) [1].
• lookup_value is what you search for, and it must sit in the first column of table_array [1].
• col_index_num counts columns from the left edge of table_array, starting at 1 [1].
• Use FALSE (or 0) for an exact match, which is what most lookups need [1].
• If the exact value is not found with FALSE, Excel returns the #N/A error [1].

Syntax

The function takes four arguments. The first three are required, and the fourth is optional but you should almost always supply it.

Written as a formula, the pattern looks like this:

$$=\text{VLOOKUP}(\text{lookup\_value},\ \text{table\_array},\ \text{col\_index\_num},\ \text{range\_lookup})$$

Microsoft describes the simplest form as: what you want to look up, where you want to look for it, the column number in the range containing the value to return, and an approximate or exact match indicated as 1/TRUE or 0/FALSE [1].

How It Works

VLOOKUP reads down the first column of your range until it finds the lookup value. Once it finds a match, it moves across that same row to the column you named and returns whatever is in that cell.

The "V" stands for vertical. The function searches down a column, so your lookup values have to be arranged vertically in the leftmost column of the range you pass in. If the value you want back sits to the left of the lookup column, VLOOKUP cannot reach it. That is one of the main reasons people switch to other tools, which are compared in Excel lookup functions: VLOOKUP, XLOOKUP and INDEX-MATCH.

The fourth argument changes the behavior completely. With FALSE, Excel looks for an exact match and returns #N/A when nothing matches [1]. With TRUE, Excel assumes the first column is sorted in ascending order and returns the closest match that does not go past the lookup value. For text lookups and ID lookups, always use FALSE.

One practical detail: when you copy a formula down a column, lock the table range with dollar signs, as in $E$2:$F$6. The lookup value should stay relative so it shifts row by row.

Worked Example

The sheet below holds a sales list in columns A to D and a separate price table in columns E and F. Column C uses VLOOKUP to fetch each product's price from the price table.

The formula in C2 does four things. It takes the product ID in A2 as the lookup value. It searches the first column of $E$2:$F$6, which is column E. It returns the value from column 2 of that range, which is column F. The FALSE at the end forces an exact match.

Because the range is locked with $ signs, you can copy C2 down to C6 and every row keeps pointing at the same price table while the lookup value moves down with it. Row 3 looks up P-102 and returns 7.25. Row 4 looks up P-104 and returns 5.5. Row 5 looks up P-101 and returns 14.5. Row 6 looks up P-103 and returns 22.

Notice that the order of the sales list does not matter. The price table is sorted by product ID, but VLOOKUP with FALSE does not care about sort order. It scans until it finds the exact match.

More Examples

Look up a value in a different workbook or sheet. Point table_array at a sheet reference, as in =VLOOKUP(A2,Products!$A$2:$D$500,3,FALSE). The same rule applies: the lookup column must be the leftmost column of the range you name.

Return a column far to the right. If your table runs from column A to column H and you want the value in H, use 8 as col_index_num, because H is the eighth column from A.

Wrap it in IFERROR for a cleaner output. =IFERROR(VLOOKUP(A2,$E$2:$F$6,2,FALSE),"Not found") shows a friendly label instead of #N/A when a product ID is missing. This is common in reports where blank or missing rows would otherwise look broken.

Look up text instead of IDs. =VLOOKUP("Fontana",B2:E7,2,FALSE) returns the value in the second column of the range for the row where the first column contains Fontana. Microsoft uses exactly this pattern to show that text lookups need quotes around the value [1].

Use it for horizontal data. If your data runs across rows instead of down columns, VLOOKUP will not work. Use How to Use HLOOKUP in Excel (With Examples) instead, since HLOOKUP searches across the top row of a range.

Errors and How to Fix Them

The #N/A error is by far the most common. In practice it usually means the lookup value and the table value look identical but are not. A number stored as text will not match a real number, and a trailing space will break an otherwise perfect match.

Common Mistakes

• Leaving out the fourth argument. If you omit range_lookup, Excel uses an approximate match, which can return a wrong value from a nearby row. Always type FALSE explicitly.
• Forgetting to lock the table range. Copying a formula down without $ signs shifts the range and produces wrong or missing results. Use $E$2:$F$6 style references.
• Counting columns from the sheet instead of the range. col_index_num counts from the left edge of table_array, not from column A. If your range starts at E, then E is column 1 [1].
• Looking up a value that sits to the right of the return column. VLOOKUP only searches the first column of the range and only returns from columns to its right. Rearrange the data or use a different function.
• Mixing text and numbers. A product ID stored as text in one table and as a number in another will never match. Convert both sides to the same type.
• Using approximate match on unsorted data. With TRUE, the first column must be sorted ascending. On unsorted data the result is unreliable.

Limitations

VLOOKUP only looks to the right. The lookup column must be the leftmost column of the range, so any value to its left is unreachable without rearranging your sheet. It also returns only the first match it finds, so duplicate lookup values silently produce the first hit and hide the rest.

The function is also fragile when columns move. If someone inserts a column inside your table range, col_index_num no longer points at the column you intended, and the formula returns the wrong data without any warning. Microsoft now recommends XLOOKUP as an improved version that works in any direction and returns exact matches by default [1]. If you are on a version that supports it, see XLOOKUP vs VLOOKUP: Differences and When to Use Each before committing to VLOOKUP for new work. For matching a position rather than returning a value, the Excel MATCH function: syntax and examples is the better fit.

Frequently Asked Questions

How do you use a VLOOKUP in Excel?

Type =VLOOKUP( and then supply four arguments separated by commas: the value to find, the range to search, the column number to return, and FALSE for an exact match [1]. Press Enter and the result appears in the cell. Copy the formula down if you need it for several rows, and lock the table range with dollar signs first.

Why does my VLOOKUP return #N/A when the value is clearly there?

The two values are almost certainly different types or contain hidden characters. A number stored as text will not match a real number, and a trailing space breaks an exact match. Check both cells with a formula that measures length, or clean the data before looking up.

Can VLOOKUP look to the left?

No. VLOOKUP searches the first column of the range you give it and returns values from columns to the right of that one [1]. If the value you want sits to the left of the lookup column, move the columns, or use INDEX and MATCH or XLOOKUP instead.

What is the difference between TRUE and FALSE in VLOOKUP?

FALSE means exact match and returns #N/A when nothing matches [1]. TRUE means approximate match, which requires the first column to be sorted ascending and returns the closest value that does not exceed the lookup value. For IDs, names and codes, use FALSE.

Does VLOOKUP work in older .xls files?

Yes. VLOOKUP has been part of Excel for many versions, so formulas built in current files open and calculate in older .xls workbooks. The function name and the four arguments are the same. Features added later, such as XLOOKUP, are not available in those older formats.

If you are building a lookup layer for a larger report, pair VLOOKUP with aggregation tools such as Excel SUMIF and SUMIFS: syntax and examples so the returned values feed straight into your totals.

References

1. VLOOKUP function | Microsoft Support

Further Reading

• Broman KW, Woo KH (2018). Data Organization in Spreadsheets. The American Statistician
• Ziemann M, Eren Y, El-Osta A (2016). Gene name errors are widespread in the scientific literature. Genome Biology
• Microsoft Support: Excel help and learning
• McCullough B, Heiser DA (2008). On the accuracy of statistical procedures in Microsoft Excel 2007. Computational Statistics & Data Analysis
• Abeysooriya M, Soria M, Kasu MS et al. (2021). Gene name errors: Lessons not learned. PLOS Computational Biology

Related Articles

• How to Use HLOOKUP in Excel (With Examples)
• Excel Lookup Functions: VLOOKUP, XLOOKUP and INDEX-MATCH
• Excel MATCH Function: Syntax and Examples
• XLOOKUP vs VLOOKUP: Differences and When to Use Each
• Excel SUBTOTAL Function: Syntax, Formulas and Examples
• Pivot Table in Excel: Step-by-Step Tutorial
• Tabular Data: What It Is and How to Analyze It