What VLOOKUP does and when you need it

VLOOKUP is an Excel function that finds a value in the leftmost column of a table and returns a value from a column to its right. You use it when you have two related lists of information and need to pull data from one list based on a match in the other.

The most common real-world example: you have a list of product codes in one column and product prices in another column. A VLOOKUP formula lets you type a product code into a cell and automatically get back the price without manually searching the table. Another example: you have employee IDs in one spreadsheet and employee names in another, and you want to fill in names based on IDs.

VLOOKUP saves time when your lists are long or when you need to do the same lookup many times. It also reduces mistakes because the formula does the searching instead of your eyes.

Key Takeaways

  • A VLOOKUP formula has four parts: the value you are looking for, the table it is in, which column number to return data from, and whether you want an exact match or approximate match.
  • The lookup value must be in the leftmost column of your table, and the return column must be to the right of it.
  • Use FALSE or 0 for exact matches (most common) and TRUE or 1 for approximate matches (only when your lookup column is sorted in ascending order).
  • Common errors like #N/A mean the lookup value was not found, and #REF! means the column number points outside your table.

The four parts of a VLOOKUP formula

Every VLOOKUP formula follows the same structure: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). Understanding each part is the key to writing one that works.

The lookup_value is what you are searching for. This can be a number, text, or a cell reference (like A2). If you type the value directly into the formula, put it in quotes if it is text: =VLOOKUP("apple", A:C, 2, FALSE). If you reference a cell, no quotes: =VLOOKUP(A2, A:C, 2, FALSE).

The table_array is the range of cells that contains both your lookup column and your return column. This must include the leftmost column where the lookup value lives. You can reference a range like A2:C100 or an entire column like A:C. Using entire columns (A:C) is simpler and works even if your data grows.

The col_index_num is the column number (counting from the left) that contains the data you want to return. If your table is A:C, column A is 1, column B is 2, and column C is 3. If you want to return data from column B, use 2. This number must never be smaller than 1 or larger than the number of columns in your table.

The range_lookup is either FALSE (or 0) for an exact match, or TRUE (or 1) for an approximate match. Use FALSE almost always. Use TRUE only when your lookup column is sorted in ascending order and you want the closest match if an exact match does not exist.

A step-by-step example with real data

Imagine you have a price list in columns A and B. Column A has product codes (101, 102, 103) and column B has prices (15.99, 22.50, 18.75). You want to type a product code in cell D2 and have the price appear in cell E2.

Click on cell E2. Type the formula: =VLOOKUP(D2, A:B, 2, FALSE). Press Enter. If D2 contains 102, the formula returns 22.50 because that is the price in column B next to product code 102 in column A.

Now copy this formula down. Click E2 again, then drag the small square at the bottom right corner of the cell down to E10 (or however many rows you need). The formula automatically adjusts: E3 becomes =VLOOKUP(D3, A:B, 2, FALSE), E4 becomes =VLOOKUP(D4, A:B, 2, FALSE), and so on. Each row looks up the code in its own D cell and returns the matching price.

Common mistakes and how to fix them

The error #N/A means the lookup value was not found in the first column of your table. Check that the value in D2 actually exists in column A. Watch for extra spaces, different capitalization, or typos. If you are looking up text, "apple" and "Apple" are different to Excel.

The error #REF! means your col_index_num is too large. If your table is A:B (only 2 columns), you cannot ask for column 3. Change the number to match the actual width of your table.

The error #VALUE! usually means you typed the formula incorrectly. Check that commas separate each part, that text values are in quotes, and that you closed the parenthesis at the end.

If the formula returns a value but it is wrong, check that your lookup column is actually the leftmost column of your table range. VLOOKUP cannot look to the left. If your data is arranged with the return column on the left and the lookup column on the right, use HLOOKUP (for horizontal lookup) or rearrange your columns.

When to use FALSE versus TRUE for the last argument

Use FALSE (or 0) in almost every situation. This tells Excel to find an exact match only. If the lookup value does not exist, you get #N/A, which tells you something is wrong.

Use TRUE (or 1) only when your lookup column is sorted in ascending order (smallest to largest) and you want the closest match if an exact match does not exist. For example, if you have a table of tax brackets where income thresholds are 0, 25000, 50000, and 100000, a TRUE lookup finds the highest threshold that does not exceed the income you are looking up. This is rare in everyday spreadsheets.

Copying formulas across rows and columns

Once you write a VLOOKUP in one cell, you can copy it to many cells at once. Click the cell with your formula, then drag the small square at the bottom right corner down (to copy down rows) or right (to copy across columns). The formula adjusts automatically.

When you copy down, the lookup_value reference changes (D2 becomes D3, D4, etc.) but the table_array stays the same. This is usually what you want. If you want the table_array to stay fixed when copying, use dollar signs: =VLOOKUP(D2, $A$2:$B$100, 2, FALSE). The $A$2:$B$100 will not change when you copy the formula, but D2 will become D3, D4, and so on.

Frequently Asked Questions

Can VLOOKUP search for text or only numbers?

VLOOKUP works with both text and numbers. If you are searching for text, put it in quotes in the formula or reference a cell that contains text. The search is case-insensitive, so "apple" and "APPLE" match the same cell.

What is the difference between VLOOKUP and HLOOKUP?

VLOOKUP searches down the leftmost column of a vertical table. HLOOKUP searches across the top row of a horizontal table. If your data is arranged with lookup values in a row instead of a column, use HLOOKUP with the same logic: =HLOOKUP(lookup_value, table_array, row_index_num, FALSE).

Why does my VLOOKUP return the wrong value even though the lookup value exists?

Check that your lookup column is the leftmost column of your table range. VLOOKUP cannot search columns to the right and return values from columns to the left. If your data is arranged that way, rearrange the columns or use INDEX and MATCH instead (a more advanced technique).

Can I use VLOOKUP to search in multiple tables at once?

No, each VLOOKUP formula searches one table. If you need to search multiple tables, write separate VLOOKUP formulas in different cells, or use an IF statement to choose which table to search based on a condition: =IF(A2="type1", VLOOKUP(B2, table1, 2, FALSE), VLOOKUP(B2, table2, 2, FALSE)).

Does VLOOKUP work if my data changes or grows?

Yes. If you use entire column references like A:C instead of a fixed range like A2:C100, the formula automatically includes new rows added to the bottom. If you add columns to the right, the formula still works as long as your return column number is correct.