VLOOKUP Drag Down Not Working? Fix the Formula

If VLOOKUP stops working or returns the wrong result when you drag it down, check that the table range is locked while the lookup-cell reference changes by row. With lookup values in A2:A100 and a lookup table in F2:G100, use =VLOOKUP(A2,$F$2:$G$100,2,FALSE) in B2, then fill it down; the next row should look up A3 in the same table.

How dragging a VLOOKUP formula works

Dragging a formula down copies it to other cells and usually adjusts relative references to match each new row. In the example, A2 becomes A3, then A4. That is normally what you want for the lookup value: each result should be based on the key in its own row.

The table range is different. It should generally stay pointed at the same lookup table. If the first formula refers to F2:G100 without dollar signs, Excel may shift that range as the formula is copied. The next row might search F3:G101, and later rows might search a range that no longer contains the first table entry. This can produce missing or incorrect results even though the formula appears to have filled correctly.

In the example formula, the dollar signs fix both the columns and rows of the table range. The lookup reference A2 has no dollar signs, so it can change as the formula is filled. The final argument, FALSE, requests an exact match, which is usually appropriate for IDs, product codes, names, and other lookup keys.

Lock the lookup table range

Inspect the formula in the first result cell before filling it down. If the table range does not contain dollar signs, edit it so its starting and ending cells remain constant. In Excel for Microsoft 365 on Windows, select a reference such as F2:G100 while editing the formula and press F4 to cycle through reference styles. You can also type the dollar signs yourself. On a Mac, you can edit the reference directly in the formula bar; keyboard shortcuts for reference styles can depend on the keyboard and Excel settings.

There is an important distinction between locking the table and locking the lookup value. In the example, A2 must be relative. If you change it to $A$2, every copied formula will keep searching for the value in A2, so each row may repeat the same result. If your lookup key is in a fixed cell outside the rows being filled, however, an absolute reference to that cell may be intentional.

For a concrete example, suppose A2 contains P-104, and F2:G100 contains product codes in column F and product names in column G. The formula in B2 searches for P-104 and returns the corresponding name from the second column of the table. When copied to B3, it searches for A3 but still uses F2:G100. That combination—moving key, fixed table—is the usual pattern.

Fill the formula down correctly

Once the first formula is correct, use the fill handle: select the formula cell and drag the small square at its lower-right corner down the column. You can also copy the cell, select the destination range, and paste. A quick alternative is to select the formula cell and the cells below it, then press Ctrl+D on Windows to fill down. On Mac, the corresponding shortcut is Command+D.

Excel may adjust references during any of these fill methods. After filling, select a lower result cell and inspect its formula. It should refer to the lookup value on that row and the same fixed table range. If you see only a repeated value rather than a formula, the source cell may have been copied as a value, or the formula may have been replaced during paste. Use the formula bar to check what the cell actually contains.

Double-clicking the fill handle can fill a formula down alongside a neighboring column of continuous data. It is convenient, but Excel uses adjacent data to decide how far to fill, so it may stop early if that data has blank cells. For a controlled fill, select the exact destination range and use Ctrl+D or copy and paste.

Check the formula’s match settings and column number

VLOOKUP searches for the lookup value in the first, leftmost column of the table range. Its column number tells Excel which column within that range to return. If the range is F2:G100, column 1 is F and column 2 is G. The number is relative to the selected table, not the worksheet column number. A return-column number larger than the table’s width can cause a #REF! error.

For most everyday lookups, use FALSE as the fourth argument to request an exact match. If that argument is omitted, VLOOKUP uses approximate-match behavior, which can return an unexpected result when the first column is not sorted appropriately. Approximate matching is useful for certain ordered bands, such as grading thresholds, but it is not a safe default for unsorted IDs or names.

If the formula is correct but returns #N/A, Excel may not find an exact match. Check that the value exists in the first column of the selected range, and confirm that the table range has not shifted. Also check whether the key is stored as text in one place and as a number in another. A code that looks like 0042 may not match a numeric 42, because leading zeros and data type can affect equality.

Diagnose common errors after filling

Compare the formula in the first row with one several rows below it. A common way to do this is to select each cell and read the formula bar. Look for these patterns:

The table range changes: add dollar signs to keep the range fixed, unless you deliberately intend each row to use a different table.
The lookup cell does not change: remove dollar signs from its row reference if every result should use the key in its own row.
The result is #N/A: verify the key exists in the first table column and check for extra spaces, different data types, or a shifted range.
The result is #REF!: check whether the return-column number exceeds the number of columns in the selected table.
Every row repeats one result: check whether the lookup cell was locked, or whether the cells contain pasted values instead of formulas.
The formula appears as text: check whether the cell is formatted as Text or the formula begins with an apostrophe. Set the cell to General, then re-enter the formula.

Also check for hidden spaces in lookup keys. For example, a code with a trailing space is different from the same code without one. If the source data is inconsistent, clean it in a helper column or correct the source values; changing the fill method will not make different text values match. If several rows fail, compare the actual lookup keys rather than assuming the formula itself is the only problem.

Make sure Excel is filling and calculating formulas

If dragging does not copy the formula at all, the fill handle may be disabled. In Excel for Microsoft 365 on Windows, open File > Options > Advanced and, under Editing options, enable “Enable fill handle and cell drag-and-drop.” In Excel for Mac, look in Excel > Settings > Edit for the fill-handle and cell-drag-and-drop setting; the exact wording can vary between releases.

If the formula is present but its result does not update after you change source data, check calculation mode. On Windows, go to Formulas > Calculation Options and select Automatic if that is appropriate for the workbook. Manual calculation can leave displayed results stale until recalculation. Pressing F9 recalculates open workbooks on Windows; on Mac, use the recalculation shortcut shown for your Excel version or the Formulas menu. Calculation mode is different from filling: it affects when formulas recalculate, not whether a formula is copied to the next row.

When to use a different lookup method

VLOOKUP is useful when the value to find is in the leftmost column of a table and the return column is to its right. It can be less convenient if columns are inserted or rearranged, because the return-column number may no longer point to the intended field. For a robust VLOOKUP, keep the table range focused and verify the return-column number whenever the table layout changes.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later, and can search in either direction without a numeric return-column index. It is an alternative, not a fix required for every drag-down problem: a correctly referenced VLOOKUP works well for many worksheets. If the workbook must be opened in older Excel versions, check compatibility before switching functions, since XLOOKUP is not supported in some older releases.

Frequently Asked Questions

Why does my VLOOKUP return the same answer in every row?
Check whether the lookup-cell reference is absolute, such as $A$2. If each row should use its own key, the row reference must change as the formula is filled. Also confirm that the cells contain formulas rather than repeated pasted values.

Should I use dollar signs in a VLOOKUP formula?
Usually, lock the table range with dollar signs so it stays fixed when copied down. Leave the lookup-cell reference relative when each row has a different key. The right choice depends on whether each reference is meant to move or stay fixed.

Why does VLOOKUP show #N/A after I drag it down?
The lookup key may not be present in the table’s first column, or the copied formula may point to a shifted range. Differences such as extra spaces, leading zeros, or text-versus-number storage can also prevent an exact match.

Why does my VLOOKUP formula show instead of a result?
The cell may be formatted as Text, or the entry may begin with an apostrophe. Change the format to General and re-enter the formula. Also check that Show Formulas is not enabled on the Formulas tab.

Can I copy VLOOKUP down without dragging?
Yes. Copy the formula cell and paste into the destination cells, or select the formula cell and the range below it and use Ctrl+D on Windows or Command+D on Mac. Check that the copied table range remains fixed.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.