What VLOOKUP does and when you need it

VLOOKUP is a formula that finds a value in one column of a table and returns a matching value from another column in the same table. You use it when you have two lists of related information and need to pull data from one list into the other without typing it manually.

A real example: you have a spreadsheet with tenant names in column A and their lease start dates in column B. In a separate area, you have a list of names you want to look up. VLOOKUP finds each name in your original table and brings back the matching lease date next to it. Without VLOOKUP, you would scroll back and forth or retype the dates by hand.

VLOOKUP only works when the information you want to return is to the right of the information you are searching for. If you need to pull data from a column to the left, you will need a different formula (HLOOKUP for horizontal tables, or INDEX and MATCH for more complex situations). For now, this guide covers the standard case: searching left-to-right in a vertical table.

Key Takeaways

  • VLOOKUP searches for a value in the leftmost column of a table and returns a value from a column to its right, using the syntax =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).
  • Your data must be organized with the search column on the left and the return column to the right; VLOOKUP cannot pull data from columns to the left of your search column.
  • The column index number tells VLOOKUP which column to return data from, counting from 1 for the leftmost column in your table range.
  • Use FALSE (or 0) for exact matches and TRUE (or 1) for approximate matches, though exact matches are correct for most everyday tasks like matching names or ID numbers.
  • Common errors like #N/A (value not found) or #REF! (broken range reference) have specific causes you can fix by checking your table structure and formula syntax.

Setting up your data so VLOOKUP can find it

VLOOKUP needs a table where the column you are searching in is the leftmost column, and the column you want to return is somewhere to the right. If your data is not arranged this way, VLOOKUP will not work.

For example, if you have a table with ID numbers in column A, names in column B, and phone numbers in column C, you can use VLOOKUP to search column A and return values from column B or C. But you cannot use VLOOKUP to search column B and return column A, because column A is to the left of column B.

Before you write your formula, check that your table has a header row (optional but helpful) and that every row has data in the search column. Empty cells in the search column will cause VLOOKUP to skip those rows or return incorrect results. If your data is messy — with extra spaces, inconsistent capitalization, or mixed data types — VLOOKUP may not find matches even when the values look identical to you.

Writing the VLOOKUP formula step by step

The VLOOKUP formula has four parts. Type it exactly as shown, with commas separating each part:

=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

lookup_value is what you are searching for. This can be a cell reference (like B2), a number, or text in quotes (like "Smith"). If you are copying the formula down a column, use a cell reference so it changes for each row.

table_array is the range of cells that contains your entire table — both the search column and the return column. Use absolute references (with dollar signs, like $A$1:$C$100) so the range does not change when you copy the formula. The leftmost column in this range is always the search column.

col_index_num is the column number you want to return data from, counting from the left. If your table is A:C and you want data from column B, the index is 2. If you want column C, the index is 3. Count only the columns inside your table_array range, not all columns in the spreadsheet.

range_lookup is either FALSE (or 0) for an exact match, or TRUE (or 1) for an approximate match. Use FALSE for most tasks — searching for a specific name, ID, or product code. Use TRUE only if your search column is sorted in ascending order and you want the closest match below your lookup value (useful for tax brackets or commission tiers).

A complete example you can follow

Suppose you have a tenant list in cells A1:B10. Column A has names (with a header "Tenant Name" in A1), and column B has move-in dates (with a header "Move-In Date" in B1). You want to look up a name in cell D2 and return the matching move-in date in cell E2.

In cell E2, type: =VLOOKUP(D2,$A$1:$B$10,2,FALSE)

This formula searches for the value in D2 (the lookup name) in the range A1:B10, returns the value from column 2 of that range (column B, the move-in date), and requires an exact match. If the name in D2 is "Maria Garcia" and Maria Garcia appears in row 5 of your table, the formula returns the date from B5.

To copy this formula down to cells E3, E4, and beyond, click E2, then drag the fill handle (the small square at the bottom-right corner of the cell) down as far as you need. The lookup_value (D2) will change to D3, D4, and so on, but the table_array ($A$1:$B$10) will stay the same because of the dollar signs.

Why VLOOKUP returns an error and how to fix it

#N/A error means VLOOKUP could not find the lookup value in the first column of your table. Check that the value you are searching for actually exists in that column. Look for extra spaces, different capitalization, or typos. If the search column contains numbers and your lookup value is text (or vice versa), VLOOKUP will not find a match. You can use the TRIM function to remove extra spaces: =VLOOKUP(TRIM(D2),$A$1:$B$10,2,FALSE).

#REF! error means your table_array reference is broken — usually because you deleted a column or the range is invalid. Check that the range you typed actually exists and that you did not accidentally delete any columns inside it.

#VALUE! error usually means you typed the col_index_num as text instead of a number, or your range_lookup is not FALSE, TRUE, 0, or 1. Check the syntax of your formula and make sure each part is the correct type.

The formula returns a value, but it is wrong. This usually happens when your table is not sorted correctly for an approximate match (range_lookup = TRUE), or when there are duplicate values in your search column and VLOOKUP is returning the first match instead of the one you expected. Switch to FALSE for exact matches, or reorganize your data so duplicates are not a problem.

When to use VLOOKUP instead of other approaches

VLOOKUP is fastest when you have a small to medium table (up to a few thousand rows) and you need to look up values repeatedly. It is also the most straightforward formula for beginners because the syntax is straightforward and the logic is straightforward to follow.

If your table is very large (tens of thousands of rows), INDEX and MATCH formulas may be slightly faster, but the difference is usually not noticeable in everyday work. If you need to search in a column to the left of your return column, use INDEX and MATCH instead. If you need to search horizontally (across columns instead of down rows), use HLOOKUP.

For one-time lookups, it is often faster to use the Find feature (Ctrl+F on Windows, Cmd+F on Mac) to locate a value manually. VLOOKUP pays off when you have dozens or hundreds of lookups to do and you want the computer to do the work.

Frequently Asked Questions

Can VLOOKUP search for partial text, like just a last name?

VLOOKUP searches for exact matches (or approximate matches in sorted data), not partial text. If you need to find "Garcia" within "Maria Garcia", use a combination of SEARCH and INDEX/MATCH instead. For straightforward cases, you can also use filtering or sorting to find what you need manually.

What is the difference between FALSE and TRUE in the last part of the formula?

FALSE finds an exact match — the lookup value must be identical to a value in the search column. TRUE finds an approximate match — it returns the largest value that is less than or equal to your lookup value, but only if the search column is sorted in ascending order. Use FALSE for names, IDs, and product codes. Use TRUE for ranges like tax brackets or commission tiers.

Can I use VLOOKUP to search across multiple sheets?

Yes. In the table_array part of your formula, include the sheet name before the range. For example: =VLOOKUP(D2,Sheet2!$A$1:$B$10,2,FALSE) searches for the value in D2 on Sheet2 in the range A1:B10. Make sure the sheet name is spelled correctly and exists in your workbook.

Why does VLOOKUP return the same value for different lookup values?

This usually means your search column has duplicate values, and VLOOKUP is returning the first match it finds. If you have multiple rows with the same name or ID, VLOOKUP will always return the value from the first matching row. Reorganize your data so each search value appears only once, or use a more advanced formula like INDEX and MATCH with additional criteria.

Can I use VLOOKUP with a formula as the lookup value?

Yes. Instead of typing a cell reference or a fixed value, you can use a formula. For example: =VLOOKUP(LEFT(D2,5),$A$1:$B$10,2,FALSE) searches for the first five characters of the value in D2. This is useful when your lookup values need to be cleaned up or transformed before searching.