If VLOOKUP works in the first row but returns wrong results or errors after you copy it down, the lookup range is often shifting. Keep the lookup cell relative, lock the table range with dollar signs, and use FALSE for an exact match unless you specifically need an approximate match.
Start by checking the copied formula
Click a cell where the copied formula fails and inspect the formula bar. Compare its references with the working formula above it. When Excel copies a formula down, it normally adjusts relative references to match the new row. That behavior is useful for the value being looked up, but it can move the table range out of place.
Suppose A2 contains 102, and E2:F4 contains the pairs 101/East, 102/Central, and 103/West, with IDs in column E and regions in column F. Enter this in B2:
=VLOOKUP(A2,$E$2:$F$4,2,FALSE)
The result is Central. The dollar signs keep the lookup table fixed when the formula is copied down, while A2 changes to A3, A4, and so on. If the formula in B3 instead refers to E3:F5, the lookup range has drifted; if it refers to E2:F4, that part is anchored correctly.
To inspect a formula without changing it, select the cell and read the formula bar. You can also double-click the cell to see the references in place, then press Esc to leave edit mode without altering the formula.
Why dollar signs matter when copying down
Excel has relative, absolute, and mixed references. A reference such as A2 is relative: copy the formula down one row and it becomes A3. A reference such as $E$2:$F$4 is absolute: both the columns and rows stay fixed. VLOOKUP usually needs a relative reference for the lookup value and an absolute reference for the table array.
Without dollar signs, a formula using E2:F4 becomes E3:F5 one row below. The first record is then left outside the range, and the last row may point beyond the intended data. This can cause #N/A or, with approximate matching, a plausible but incorrect result. A range that happens to work in the first row can therefore fail only after the formula is filled down.
To add or remove reference locks while editing a formula, select the reference and press F4 on Windows. Excel cycles through relative, absolute, and mixed forms. On many Mac keyboards, use Command+T; depending on keyboard settings, you may need Fn with the key. You can also type the dollar signs yourself, which is often the clearest option.
Mixed references are useful in some copied formulas, but a standard VLOOKUP copied vertically generally needs the whole table range locked. Do not lock the lookup value if each row should search for its own ID.
Check VLOOKUP’s arguments and match setting
VLOOKUP uses four arguments: the value to find, the table range, the number of the return column within that range, and whether to use an approximate match. The return-column number starts at the left edge of the selected range, not at column A of the worksheet. If the range is E:F, column E is 1 and column F is 2.
For most ID, name, product, and account lookups, use FALSE as the fourth argument. It requests an exact match. If you leave that argument out, VLOOKUP defaults to approximate matching, which expects the first column of the table to be sorted in ascending order. An unsorted list with approximate matching can return an unexpected nearby value rather than an error.
Check that the return-column number is valid for the selected table range. For example, a range with only two columns cannot return column 3. Also remember that VLOOKUP searches only the first column of its table range and returns a value from a column to its right. If the field you want is to the left of the lookup column, VLOOKUP is not the right arrangement without changing the range or using another lookup method.
Exact matching does not mean the values merely look alike. Excel must recognize the lookup value and first-column value as equivalent. Differences in data type, spaces, or characters can prevent a match even when both cells display the same text.
Diagnose errors and wrong results
The error or symptom often points to the cause. Check these issues in order rather than rebuilding the formula immediately:
- #N/A: Excel did not find an exact match. Confirm that the lookup value exists in the first column of the range, check for hidden spaces or number-versus-text differences, and verify that the range still covers the source row.
- #REF!: The return-column number refers to a column outside the selected table range, or part of a reference was removed. Count columns from the left edge of the VLOOKUP range.
- A repeated or wrong result: The lookup cell may have been made absolute when it should change by row, the return-column number may be off, or approximate matching may be in use.
- Correct in one row, wrong farther down: Inspect whether the table range moved, whether later lookup values are absent, and whether the formula was edited inconsistently partway through the column.
- The formula is displayed instead of a result: The cell may be formatted as Text, or the entry may start with an apostrophe. Change the format to General, then re-enter the formula; changing the format alone may not make an existing text entry calculate.
A quick way to compare formulas is to select a working cell and a failing cell one at a time, then check the formula bar. The intended change is usually the lookup cell reference; the table range and return-column number should normally stay the same throughout a filled-down column.
Resolve text, number, and spacing mismatches
A frequent source of #N/A is a number stored as text on one side and as a numeric value on the other. For example, an ID displayed as 102 may be text in the lookup cell but a number in the source list. Formatting both cells to show zero decimal places does not necessarily convert the underlying values. Check for warning indicators, alignment differences, or the Number Format setting, but confirm the underlying data rather than relying only on appearance.
Extra spaces can cause a similar mismatch. A space at the beginning or end of a text ID is easy to miss, and a nonbreaking space copied from a website may not be removed by ordinary cleanup. Compare the source and lookup values directly, or create cleaned helper columns. If you use a helper formula, place the cleaned result alongside the original data and use that cleaned column consistently as the lookup key. Avoid changing IDs with leading zeros into numbers if those zeros are meaningful, such as in postal codes or product codes.
VLOOKUP is not case-sensitive, so capitalization alone will not distinguish two keys. It also treats wildcard characters specially in exact text lookups: an asterisk can match any sequence of characters and a question mark can match one character. If those symbols are literal parts of an ID, check the data carefully and use a lookup approach suited to the required matching behavior.
Fill the formula down and check calculation
Once the first formula is correct, select its cell and use the fill handle, the small square at the lower-right corner of the selection, to drag it down. If an adjacent column has a continuous list of data, double-clicking the fill handle usually fills the formula to the end of that adjacent data region. Check the last few rows afterward, especially if there are blank rows or multiple blocks of records.
You can also select the formula cell and the cells below it, then choose Home > Fill > Down. On Windows, Ctrl+D fills down in a selected range; Excel for Mac also commonly uses Control+D, though keyboard settings can affect shortcuts. Copy and paste works too, but filling a selected range makes it easier to apply the same formula to the intended rows.
If a formula changes but its displayed result does not, check calculation mode: choose Formulas > Calculation Options > Automatic. In Manual mode, Excel may not recalculate every formula immediately. Press F9 on Windows to recalculate; Mac keyboard behavior can vary, and you may need Fn+F9. Also confirm that the workbook is not showing formulas in cells instead of results, which can make a correct formula appear not to calculate.
Use an Excel Table or a lookup alternative
If the source data grows regularly, converting it to an Excel Table can reduce range-maintenance problems. Select a cell in the source data and choose Insert > Table, then give the table a clear name in the Table Design tab. Tables expand as rows are added, so a formula does not depend on a fixed endpoint such as row 100. Keep the lookup key in the table’s first column when using VLOOKUP.
For example, assume the current row is in a table with a column named ID, and a separate table named LookupTable has ID in its first column and Region in its second. In the current table’s Region column, use:
=VLOOKUP([@ID],LookupTable,2,FALSE)
If the current row’s ID is 102 and the matching row in LookupTable has Region set to Central, the formula returns Central. The structured reference [@ID] means the ID in this row, so Excel can fill the formula through the table column while the named lookup table remains the source.
In Microsoft 365 and Excel 2021 or later, XLOOKUP is another option. It can search one column and return a value from another without counting a return-column index, and it can look to either side. Assume A2 contains 102, E2:E4 contains IDs 101, 102, and 103, and F2:F4 contains East, Central, and West. This returns Central:
=XLOOKUP(A2,$E$2:$E$4,$F$2:$F$4)
Still lock the source ranges if you copy that formula down. XLOOKUP is not available in some older Excel versions, so a workbook shared with those versions may need VLOOKUP or another compatible method.
Frequently Asked Questions
Why does VLOOKUP work in the first row but show #N/A below?
Most often, the table range shifted when copied, or the later lookup value is missing or formatted differently from the source key. Compare the copied formula with the working one and lock the table range while leaving the lookup-cell reference relative.
Should I use TRUE or FALSE in VLOOKUP?
Use FALSE for an exact match, which is the usual choice for IDs, names, and product codes. TRUE or an omitted fourth argument requests approximate matching and requires the first table column to be sorted in ascending order.
How do I keep the VLOOKUP range from changing when I drag down?
Add dollar signs to both endpoints of the range, such as $E$2:$F$100. Keep the lookup reference, such as A2, unlocked if each row should search for a different value.
Why does VLOOKUP return the wrong column?
The third argument counts columns from the first column in the selected table range. If the range begins in E, then E is column 1 and F is column 2, regardless of their worksheet column letters.
Can VLOOKUP return a value from a column to the left?
No. VLOOKUP searches the first column of its selected range and returns from a column to its right. Reorder the data if appropriate, or use XLOOKUP in a supported Excel version to search and return from separate columns.
Related
- Excel Template for a Social Media Calendar
- Excel Job List Template: Track Applications and Follow-Ups
- Excel To-Do List Templates: How to Choose and Use One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel XLOOKUP Spill Error: Causes, Fixes, and ExamplesAn Excel XLOOKUP spill error usually means the formula is returning multiple results but Excel cannot place them…
- Google Sheets Date Format: How to Change and Fix ItTo change a date’s appearance in Google Sheets, select the cell or range and choose Format > Number…
- Excel Shortcut Keys Not Working? Fix the Common CausesIf Excel shortcut keys are not working, first check whether you are editing a cell, using Excel for…
- Error Saving Excel File? Common Causes and Reliable FixesIf Excel says it cannot save, choose File > Save As > Browse and save a new copy…
- Excel UNIQUE Formula Not Working? Fix Common IssuesIf Excel’s UNIQUE formula is not working, first check that you have Microsoft 365 or Excel 2021 or…
- Excel Workbook Protection: What Is Locked and How to Fix ItIf an Excel workbook is protected, Excel is restricting an action because a worksheet, the workbook structure, or…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.