VLOOKUP Between Sheets Not Working? How to Fix It

If VLOOKUP between sheets is not working, check that the lookup value is in the first column of the selected range, the sheet reference is correct, and the formula uses an exact match. For example, with the lookup key in Orders!C2 and matching keys and results in Products!A2:B100, enter =VLOOKUP(C2,Products!$A$2:$B$100,2,FALSE) in Orders; it returns the corresponding value from the second column of Products, or #N/A if no exact match exists.

In Excel for Microsoft 365 on Windows, a formula can refer to another worksheet in the same workbook just as it refers to a range on the current sheet. Most failures come from a mismatch between the values being compared, a range that does not include the needed column, or a formula set to approximate matching unintentionally. The checks below help identify which issue applies.

How VLOOKUP works across worksheets

VLOOKUP searches down the first column of a range for a value, then returns a value from another column in the same row. In the example, Products!$A$2:$B$100 is the table range: column A contains product IDs and column B contains product names. The 2 tells Excel to return the second column of that range. FALSE tells it to look for an exact match.

The worksheet name and range are joined with an exclamation mark. A simple sheet name can be written as Products!A2:B100. If the name contains spaces or certain punctuation, enclose it in single quotation marks. For example, with the lookup key in Orders!C2 and the product ID and name in Product List!A2:B100, the formula is =VLOOKUP(C2,'Product List'!$A$2:$B$100,2,FALSE). The single quotation marks belong around the sheet name, not the cell range.

VLOOKUP does not search every column in the range. Its lookup column must be the range’s leftmost column, and its result column must be to the right. If the ID is in column B but the desired description is in column A, VLOOKUP cannot return the value to its left using that range arrangement. This limitation is often mistaken for a broken sheet reference.

Build and check the formula step by step

To reduce typing errors, start the formula in the sheet where you want the answer. Type =VLOOKUP(, select the cell holding the lookup value, enter a comma, then select the table range on the other sheet. Finish by entering the result-column number and FALSE for an exact match. Press Enter. Excel inserts the cross-sheet reference as you select the range.

Confirm the value to search for, such as a product ID in Orders!C2.
On the lookup sheet, identify the column containing those IDs. It must be the first column in the chosen range.
Select a range that includes both the lookup column and the column containing the answer.
Count the return column from the left edge of the selected range. If the answer is in the second column, use 2.
Use FALSE for an exact match in most ID, name, and code lookups.
Test the formula with a key you can locate manually on the other sheet.

Dollar signs make a reference absolute, so the range stays fixed when you copy the formula down. In the example, with each order’s key in C2, product IDs and names in Products!A2:B100, and the formula being filled down, use =VLOOKUP(C2,Products!$A$2:$B$100,2,FALSE). The lookup cell remains relative, changing to C3 on the next row, while the table range remains A2:B100. On Windows, F4 cycles through reference styles while editing a selected cell reference in a formula. On Mac, the equivalent is commonly Command+T, though keyboard settings and function-key behavior can affect the shortcut.

Fix #N/A and values that look identical

#N/A means Excel did not find a match under the formula’s matching rules. A frequent cause is that one key is stored as text and the other as a number. A cell displaying 123 can hold numeric 123 on one sheet and text “123” on another; those values can look alike but fail an exact lookup. Check a few source cells’ alignment and number format, but do not rely on appearance alone: number formatting does not always reveal the stored value.

Leading zeros create a related problem. If an imported ID is text “00123” but the lookup value is numeric 123, they are not identical. Decide whether the identifier should be treated as text or as a number, then make both columns consistent. For codes and account-like IDs, preserving leading zeros as text is often important. Changing a cell’s display format after import may not convert the underlying value.

Spaces and nonprinting characters are another common source of failed matches. A trailing space in “North ” is different from “North,” and imported data may include characters that are difficult to see. Excel’s TRIM function can remove ordinary leading and trailing spaces and reduce repeated spaces between words, but it does not remove every kind of nonbreaking or hidden character. Cleaning a helper column on both sheets, then looking up those cleaned values, is safer than assuming visually identical text is identical data.

Also verify that the lookup value actually exists in the selected range. It may be missing, misspelled, or outside the range because new rows were added below its last row. If the formula uses a fixed range such as A2:B100 and the data now extends to row 140, keys below row 100 cannot be found. Expand the range or use an Excel Table so added records can be included more reliably.

Check the range, column number, and match mode

A formula can point to the correct sheet and still return the wrong answer if the range starts in the wrong column. Suppose product IDs are in column A and names are in column B. If the selected range begins at B instead of A, VLOOKUP searches names, not IDs. Likewise, the column number is counted from the left edge of the selected range, not from the worksheet’s column letters. If the range is C:F, its second column is D, even though D is the fourth worksheet column.

Check for #REF! if the formula’s column number is greater than the number of columns in the selected range. For instance, a range with two columns cannot return column 3. Check for #VALUE! if the column number is invalid, such as zero or a negative number. Correct the range or the return-column number rather than changing the lookup data.

The final argument controls matching. FALSE requests an exact match. TRUE, or leaving that argument out, permits approximate matching, which is designed for ranges such as tax bands or grading thresholds and generally requires the first column to be sorted ascending. Approximate matching can return a plausible but incorrect result when the data is unsorted or when the task requires an exact ID. It is not a fix for #N/A in an ordinary exact lookup.

Understand sheet references and copied formulas

Worksheet references can break when a sheet is renamed, deleted, or moved between workbooks. Excel usually updates references when a sheet in the same workbook is renamed, but a manually typed name can contain a spelling error from the start. Check the formula bar and compare the sheet name with the tab. A reference to a sheet with spaces needs single quotation marks; a reference to a sheet without spaces can use them too, but they are not required.

When copying a formula down, inspect whether the lookup range moved. A range written as Products!A2:B100 is relative, so it can shift as the formula is filled down. Lock it as Products!$A$2:$B$100 when each row must search the same list. Conversely, do not lock the lookup cell if each output row should use a different key. These reference choices explain many formulas that work in the first row but fail or return different results in later rows.

If the sheets are in separate workbooks rather than separate tabs in one workbook, the reference may include the other workbook’s name and path. External links can be affected if that file is renamed, moved, or unavailable. For a stable lookup, consider placing the source table in the same workbook or updating the link through Excel’s workbook-link controls. A two-sheet lookup within one workbook does not require an external workbook link.

Use a practical troubleshooting sequence

When the cause is unclear, work from the formula structure toward the data rather than rewriting the whole formula at random. Select the formula cell and examine its arguments in the formula bar. Confirm the lookup cell, sheet name, range boundaries, return-column index, and FALSE setting. Then test one key manually against the first column of the range.

If the result is #N/A, compare the stored values, check spaces and hidden characters, and confirm the key is inside the range.
If the result is #REF!, check whether the result-column number exceeds the width of the selected range or a referenced sheet was deleted.
If the result is a value from the wrong row or seems unexpectedly close, make sure the last argument is FALSE rather than TRUE or omitted.
If the first row works but later rows do not, inspect whether the table range shifted when the formula was copied.
If the formula itself appears as text in the cell, change the cell format from Text to General, then re-enter the formula. Also check that Show Formulas is not enabled.

Do not wrap every lookup in IFERROR before diagnosing it. IFERROR can replace an error with a message, but it can also conceal a broken reference or bad column number that should be corrected. Once the formula is sound, a user-friendly message can be appropriate. With the lookup key in Orders!C2 and product IDs and names in Products!A2:B100, this formula displays Not found when no match is returned: =IFERROR(VLOOKUP(C2,Products!$A$2:$B$100,2,FALSE),"Not found"). It returns the product name for a matching ID and the text Not found for an error, so use it only when that fallback is meaningful.

When to use XLOOKUP instead

VLOOKUP remains useful for older workbooks and straightforward tables, but XLOOKUP avoids the requirement that the lookup column be leftmost and does not use a counted return-column number. XLOOKUP is available in Microsoft 365 and Excel 2021 or later. With the lookup key in Orders!C2, product IDs in Products!A2:A100, and names in Products!B2:B100, this formula returns the matching name or Not found: =XLOOKUP(C2,Products!$A$2:$A$100,Products!$B$2:$B$100,"Not found",0). The final 0 specifies exact matching.

XLOOKUP is not a universal repair for inconsistent data. It can still fail to match text against numbers, keys with extra spaces, or a reference that excludes the relevant row. It is a useful alternative when the return column is to the left of the lookup column, when you want a not-found result built into the function, or when a long table makes VLOOKUP’s column counting error-prone. If a workbook must be opened in an older Excel version that does not support XLOOKUP, retain a compatible function such as VLOOKUP.

Frequently Asked Questions

Why does VLOOKUP work on one sheet but not another? A formula can work with one source range and fail with another because the second sheet has different data types, extra spaces, a different column order, or a range that excludes the key. Check the lookup column and compare the actual stored values, not just how they look.

Does VLOOKUP work between two sheets in the same workbook? Yes. Include the other sheet’s name and an exclamation mark before the range, such as Products!A2:B100. Put single quotation marks around a sheet name containing spaces, as in ‘Product List’!A2:B100.

Why does VLOOKUP return #N/A when the value is there? The visible value may not exactly equal the lookup value. Common reasons include number-versus-text differences, leading zeros, spaces, nonprinting characters, or a lookup range that stops before the row containing the key. Confirm the last argument is FALSE for an exact lookup.

Does the lookup column have to be the first column? Yes. VLOOKUP searches only the first column of its table range and returns values from columns to its right. To return a value located to the left, rearrange the range or use XLOOKUP in a supported Excel version.

Should I use TRUE or FALSE in VLOOKUP? Use FALSE for exact matches such as IDs, names, or codes. TRUE is for approximate matches in an appropriately sorted first column, such as a threshold table. Leaving the argument out defaults to approximate matching, which is why explicitly writing FALSE prevents a common source of surprising results.

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.