VLOOKUP in Excel: Formula, Syntax and Examples

By Dr. Zubair Khalid, DVM, MS, PhD ·

VLOOKUP in Excel: Formula, Syntax and Examples

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].

What Is VLOOKUP in Excel?

VLOOKUP is Excel's vertical lookup function: it searches down the first column of a range for a value you name and returns a value from another column in the same row. The formula is =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup), where the first three arguments are required and the fourth sets exact or approximate matching [1]. For almost every real lookup, such as a product ID, a name, or an invoice number, you want an exact match, so you type FALSE (or 0) as the last argument [1].

The lookup value is matched against the leftmost column of the range you supply. The column number counts from the left edge of that range, starting at 1, so if your range begins in column E, then E is column 1 and F is column 2 [1]. This is the single most common source of confusion: the number is relative to the range, not to the worksheet.

What you want to doHow to write it
Find an exact match=VLOOKUP(A2,$E$2:$F$6,2,FALSE)
Return a column further rightRaise the third number, for example 8 for column H when the range starts at A
Show a friendly message when nothing matchesWrap it: =IFERROR(VLOOKUP(...),"Not found")
Look up a text valuePut the text in quotes, as in =VLOOKUP("Fontana",B2:E7,2,FALSE) [1]

Two habits make the formula reliable. First, always type the fourth argument rather than leaving it out, because Excel then assumes an approximate match and can return a value from a nearby row. Second, lock the range with dollar signs, as in $E$2:$F$6, so copying the formula down keeps every row pointed at the same table while the lookup value shifts row by row.

If the exact value is not found with FALSE, Excel returns #N/A [1]. That error usually means the two values look identical but are not: a number stored as text will not match a real number, and a trailing space breaks an otherwise perfect match. VLOOKUP searches only to the right of the lookup column, so a value sitting to the left of it cannot be returned without rearranging the sheet. When you need to search in any direction, XLOOKUP is the modern replacement Microsoft recommends [1].

Syntax

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

ArgumentRequired?Meaning
lookup_valueYesThe value you want to find. It is matched against the first column of table_array [1].
table_arrayYesThe range or table that contains the data. The lookup column must be the leftmost column of this range [1].
col_index_numYesThe column number within table_array that holds the value to return. Column 1 is the first column of the range [1].
range_lookupNoTRUE or 1 for an approximate match, FALSE or 0 for an exact match [1].

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.

RowABCDEF
1Product IDProductPriceSales RepProduct IDPrice
2P-100Widget=VLOOKUP(A2,$E$2:$F$6,2,FALSE) -> displays $9.99AnaP-1009.99
3P-102Gadget=VLOOKUP(A3,$E$2:$F$6,2,FALSE) -> displays $7.25BenP-10114.5
4P-104Gizmo=VLOOKUP(A4,$E$2:$F$6,2,FALSE) -> displays $5.50CaraP-1027.25
5P-101Doohickey=VLOOKUP(A5,$E$2:$F$6,2,FALSE) -> displays $14.50DanP-10322
6P-103Thingamajig=VLOOKUP(A6,$E$2:$F$6,2,FALSE) -> displays $22.00EveP-1045.5

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

ErrorWhat it meansFix
#N/AThe exact value was not found with FALSE [1].Check for trailing spaces, mismatched data types, or a value that genuinely is not in the table.
#REF!col_index_num is larger than the number of columns in table_array [1].Count the columns in your range and lower the number.
#VALUE!col_index_num is less than 1 [1].Use a column number of 1 or more.
#NAME?The formula is usually missing quotes around a text value [1].Put quotes around text, as in =VLOOKUP("Fontana",B2:E7,2,FALSE) [1].

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.

Reading the Formula Out Loud

A useful way to stop mixing up the arguments is to say the formula as a sentence. For =VLOOKUP(A2,$E$2:$F$6,2,FALSE) you would say: "Look for the value in A2, search the first column of the range E2 to F6, give me the second column of that range, and I want an exact match." Every argument has one job, and the sentence keeps them in order.

The order matters because the function is positional. Excel does not read labels; it reads slots. If you swap the range and the column number, you get an error rather than a helpful message. The correct arrangement is always the value to find first, the range second, the return column third, and the match type last. A common wrong version puts the column number first, which Excel rejects because a single number cannot be searched as a range.

The match type is the only optional slot, and it is the one people skip. When it is missing, Excel behaves as if you typed TRUE, which means approximate match [1]. That setting assumes the first column is sorted ascending and returns the closest value that does not go past the lookup value. On an unsorted ID list this can quietly return the wrong row, so the safe habit is to close every formula with FALSE.

Worked Examples That Show the Mechanics

A price lookup with a locked range

Suppose a price table sits in E2:F6 with product IDs in E and prices in F, and a sales list runs down column A. In C2 you write =VLOOKUP(A2,$E$2:$F$6,2,FALSE). The dollar signs freeze the range. When you copy C2 down to C6, the lookup value becomes A3, A4, and so on, but the range stays E2:F6. Each row fetches the price for its own product ID, and the order of the sales list is irrelevant because an exact match scans until it finds the value.

Returning a column far to the right

If your table spans A to H and you want the value in H, the third argument is 8, because H is the eighth column counting from A. If instead your range starts at C and runs to H, H is the sixth column of that range, so the number is 6. The number always counts from the left edge of the range you typed, never from column A of the sheet [1].

A text lookup

To find a name rather than an ID, put the text in quotes: =VLOOKUP("Fontana",B2:E7,2,FALSE) returns the value in the second column of the range for the row whose first column contains Fontana [1]. Without the quotes, Excel treats the word as a defined name and returns #NAME? [1].

A lookup across sheets

Point the range at another sheet with =VLOOKUP(A2,Products!$A$2:$D$500,3,FALSE). The same rule holds: the lookup column must be the leftmost column of the named range, and the column number counts from that left edge.

Exact Versus Approximate Matching

The two settings behave very differently, and choosing the wrong one is the most damaging mistake with this function.

With exact matching, Excel looks for a value identical to the lookup value and returns #N/A if none exists [1]. This is what you want for IDs, names, codes, and any lookup where a near miss is meaningless. The first column does not need to be sorted.

With approximate matching, Excel assumes the first column is sorted ascending and returns the closest match that does not exceed the lookup value. This is designed for graded scales, such as tax brackets or letter grades, where you want the band a number falls into. On unsorted data the result is unreliable, and on ID lists it can return a neighbouring row without any warning.

A quick test tells you which setting a formula is using. If the last argument is FALSE or 0, it is exact. If it is TRUE, 1, or missing, it is approximate [1].

Common Misconceptions

"The column number counts from column A." It does not. It counts from the left edge of the range you supply [1]. A range starting at E makes E column 1.

"VLOOKUP can return any column in the sheet." It can only return columns to the right of the lookup column, because it searches the first column of the range and moves rightward. Values to the left are unreachable without rearranging the data.

"Duplicates are handled automatically." The function returns only the first match it finds. If two rows share a lookup value, the second is silently ignored, which can hide data problems in reports.

"Inserting a column is harmless." If someone inserts a column inside your table range, the column number no longer points where you intended, and the formula returns the wrong data with no warning. This fragility is one reason Microsoft now recommends XLOOKUP for new work [1].

"A trailing space does not matter." It does. A cell containing "P-100 " will not match "P-100", and the result is #N/A even though the values look identical on screen.

"Approximate match is a fuzzy search." It is not. It is a sorted-range band lookup, not a similarity search, and it will not find misspellings or partial text.

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

Related Articles

Related articles