VLOOKUP Not Working Across Sheets? Fix the Causes

Yes—VLOOKUP works across worksheets in the same Excel workbook. Check that the formula points to the correct sheet and range, that the lookup value is in the range’s first column, and that the formula uses an exact match when appropriate. For example, with a lookup key in A2 and the matching data on a sheet named Products, use =VLOOKUP(A2,Products!$A$2:$D$100,3,FALSE).

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. The worksheet reference tells Excel where to find that range; it does not change how the lookup works. The general structure is =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]). The final argument, in brackets in Excel’s function description, is optional—but leaving it out can produce unexpected results.

Suppose a Sales sheet has a product code in A2, and a Products sheet has product codes in column A, names in B, categories in C, and prices in D. With code P-104 in Sales!A2 and its category listed as Hardware on the Products sheet, the example formula above returns Hardware. The number 3 means “return the third column of the selected range,” not “return worksheet column C” in every possible setup.

When a sheet name contains spaces or certain punctuation, enclose it in single quotation marks. With the same Sales!A2 key and a lookup table on a sheet named Product List, the equivalent formula is =VLOOKUP(A2,'Product List'!$A$2:$D$100,3,FALSE). Excel often adds the quotation marks automatically when you select a range on a sheet with a space in its name.

Check the lookup range and column number

The lookup value must be in the leftmost column of the range supplied as table_array. If the product codes are in column B but the formula’s range begins in column A, VLOOKUP searches column A instead. Adjust the range so its first column contains the key. Also make sure the return column is inside that range: a range from A to C has three columns, so a column index of 4 is invalid.

Here is a reliable way to build the formula instead of typing the reference from memory:

Click the cell where you want the result and type =VLOOKUP(.
Select the cell containing the lookup value, type a comma, and select the lookup range on the other sheet. Include the key column as the range’s first column.
Type a comma and the position of the return column within that selected range. Type another comma and FALSE for an exact match, then close the parenthesis.
Press Enter and check the result against a row you can identify in the source data.

Dollar signs make a reference absolute, so it will not shift when you copy the formula down. With lookup codes in Sales!A2:A20 and the fixed product table in Products!A2:D100, use =VLOOKUP(A2,Products!$A$2:$D$100,3,FALSE). For a row where A2 contains P-104 and the matching category is Hardware, the result is Hardware. On Windows, F4 cycles through reference styles while editing a formula. Laptop function-key settings can affect whether you need to press Fn as well.

Use exact match unless approximate match is intentional

For most lists of product codes, employee IDs, names, or invoice numbers, use FALSE as the fourth argument. It tells VLOOKUP to find an exact match. You can use 0 for the same setting. If you omit the argument or use TRUE, VLOOKUP uses approximate matching. That mode is intended for sorted ranges and threshold lookups, such as assigning a rate based on a score band—not for finding an arbitrary ID in an unsorted list.

Approximate match can appear to work while returning the wrong person’s or product’s information, which makes it especially easy to overlook. For example, an unsorted code list may produce a nearby result rather than the row you intended. If you genuinely need approximate matching, sort the first column in ascending order and check that the lookup logic suits your data. For ordinary cross-sheet lookups, an explicit FALSE avoids relying on the default.

Diagnose common errors and wrong results

The error message and the formula’s structure offer useful clues. Fix the underlying issue before wrapping the formula in an error-handling function; otherwise, a genuine problem can be hidden.

#N/A: Excel did not find an exact match, or the formula is using exact match and the key differs. Check for extra spaces, different number-versus-text types, missing leading zeros, and a range that does not include the relevant row.
#REF!: A reference may point to a deleted or invalid range, or the return-column index may be larger than the number of columns in the selected table.
#VALUE!: Check that the column index is a valid positive number and that the formula arguments are in the right order.
#NAME?: Check the function name and any text or sheet references in the formula. A sheet name with spaces needs the correct quotation marks.
A plausible but incorrect result: Confirm the fourth argument is FALSE, the first column of the range contains the key, and the column index points to the intended return field.

Text that looks identical may not be identical to Excel. A code stored as the number 104 does not necessarily match the text value 104. A code with a leading zero, such as 00104, is particularly vulnerable if one source stores it as a number and another stores it as text: numbers do not retain leading zeros as part of their value. Compare the source cells’ underlying values and data types, not only their displayed appearance. Converting a whole column to numbers or text can help, but choose the type that preserves the meaning of the key.

Extra spaces are another frequent cause. A copied value may contain leading or trailing spaces, or a nonbreaking space from a web page. Excel’s TRIM function can remove ordinary extra spaces from text, but it does not remove every character that may look like a space. If cleaning the source data, use a helper column, inspect the cleaned results, and apply the same approach to both the lookup keys and the lookup table.

Test the reference before changing the data

Work through a short diagnosis in this order. First, click the formula cell and inspect the formula bar. Confirm the sheet name, range boundaries, column number, and final match argument. Next, open the source sheet and locate the expected key in the first column of that exact range. Then compare the key cells themselves: check whether both are numbers or both are text, and look for spaces or inconsistent punctuation. Finally, test with a key whose matching row and return value you already know.

If the formula works in the first row but gives wrong results after filling down, look for a range that moved. A relative reference such as Products!A2:D100 changes as the formula is copied; an absolute reference such as Products!$A$2:$D$100 stays fixed. The lookup cell should usually remain relative so it changes from A2 to A3, while the source table should remain fixed.

For a temporary check, you can also select the formula cell and inspect each reference in the formula bar. If you edit a formula by selecting a source range with the mouse, Excel inserts the sheet reference for you. This can catch a misspelled tab name or a range selected from the wrong worksheet. After correcting the formula, recalculate if the workbook is set to manual calculation: on Windows, use Formulas > Calculation Options and select Automatic, if that is the intended workbook setting.

When the lookup table is in another workbook

VLOOKUP can also refer to a range in a different workbook. This is distinct from looking across tabs in one file: the formula has an external workbook reference, and the linked file’s location matters. Moving, renaming, or deleting the source workbook can leave the reference broken or prompt Excel to ask whether links should be updated. If the source file is on a shared drive or cloud location, access permissions and whether the file is available can also affect the link.

To reduce reference mistakes, open both workbooks while creating the formula, start typing the function in the destination workbook, and select the lookup range directly in the source workbook. Excel will construct the external reference. Save both files, then close and reopen the destination workbook to confirm the link still points to the intended source. If colleagues will use the file, agree on a stable shared location for the source rather than relying on a path that exists only on one person’s computer.

When the lookup data is on another sheet in the same workbook, the external-link issues do not apply. You still need the correct worksheet name and range, but renaming a worksheet within Excel normally updates its references. If a formula shows #REF! after a sheet or range was deleted, recreate the intended range reference rather than assuming the error is a formatting problem.

When XLOOKUP or another approach is better

VLOOKUP is useful when the key is in the leftmost column of a rectangular table and the return value is to its right. Its limitation is that the column number is counted from the start of the selected range. If someone inserts or rearranges columns, a fixed index can point to a different field than intended. VLOOKUP also cannot return a value from a column to the left of the key.

In Microsoft 365 for Windows, XLOOKUP can search one range and return a corresponding value from a separate range. It performs exact matching by default and does not require a numeric return-column index. With the lookup code in Sales!A2, codes in Products!A2:A100, and categories in Products!C2:C100, use =XLOOKUP(A2,Products!$A$2:$A$100,Products!$C$2:$C$100,"Not found"). If A2 is P-104 and that code’s category is Hardware, the result is Hardware; if there is no match, the formula returns Not found. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, but it is not supported in older Excel versions.

INDEX and MATCH are another option in Excel versions that support those functions. They can return values from either side of a key column, but their formulas are less straightforward for some users. If a lookup is part of a repeatable import or transformation process rather than a cell-by-cell calculation, Power Query may be worth considering. For a simple two-sheet lookup, correcting the VLOOKUP reference and match setting is usually the quickest solution.

Frequently Asked Questions

Can VLOOKUP search a different sheet in the same workbook?
Yes. Include the worksheet name and an exclamation mark before the range, as in Products!$A$2:$D$100. Use single quotation marks around a sheet name that contains spaces, such as 'Product List'!$A$2:$D$100.

Why does VLOOKUP return #N/A across sheets?
Usually, no exact match exists in the first column of the specified range. Check that the range covers the row, that the key values have matching types, and that neither value has extra spaces or different leading zeros. Also confirm the formula is searching the intended sheet and column.

Does VLOOKUP work between two separate Excel files?
Yes. It can use a range in another workbook, but the reference depends on that workbook and its location. Create the link by selecting the source range while both workbooks are open, then save and verify the files in their expected locations.

Why does VLOOKUP return the wrong column?
The third argument counts columns from the left edge of table_array. If the selected range starts in column B, then B is column 1 for that formula, C is 2, and D is 3. Recount from the first column of the range, not from worksheet column A.

Should I use TRUE or FALSE for VLOOKUP?
Use FALSE for an exact match, which is appropriate for most IDs and code lookups. Use TRUE only when approximate matching is intended and the first column is sorted in ascending order for the lookup logic you need.

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.