How VLOOKUP Works Between Two Excel Sheets

Formulas & functions · Updated November 20, 2026

To look up a value from one Excel sheet in a table on another, use VLOOKUP with the lookup cell, a range on the other sheet, the number of the column to return, and FALSE for an exact match. For example, if the current sheet has a product code in A2 and a sheet named Products stores codes in column A and prices in column C, use =VLOOKUP(A2,Products!$A$2:$C$100,3,FALSE). It searches for the code in Products column A and returns the matching price from column C.

What VLOOKUP does across sheets

VLOOKUP means vertical lookup: it searches down the first column of a specified range for a value, then returns a value from another column in the same row. The lookup table does not have to be on the active sheet. A worksheet reference in the range argument tells Excel where to search, while the first argument can refer to a cell on the sheet where you are entering the formula.

A common setup is an order sheet that contains product codes and a product list on a separate sheet. Instead of copying product names or prices by hand, a formula can match each order code to the list and bring back the corresponding detail. The sheets remain separate; VLOOKUP connects them through a shared value, such as a code, ID, or name.

VLOOKUP does not compare entire rows or infer which column you mean. You define the lookup column and the return column through the selected range and column number. It also searches only from left to right within that range: the lookup column must be its leftmost column, and the return column must be to its right.

Understand the four VLOOKUP arguments

The function’s syntax is =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]). The square brackets in Excel’s syntax documentation indicate an optional argument; they are not typed as part of the formula. The arguments mean:

  • lookup_value: The value to find, often a cell reference such as A2 on the current sheet.
  • table_array: The range containing both the lookup column and the column to return. It can be on another worksheet.
  • col_index_num: The position of the return column counted from the left edge of table_array. The first column is 1, not 0.
  • range_lookup: FALSE requests an exact match; TRUE requests an approximate match. If omitted, Excel uses TRUE, so it is usually safer to specify FALSE for codes and IDs.

The column number is relative to the selected range, not the worksheet’s column letters. If the range starts in column B and you want the value in worksheet column D, the return column number is 3: B is first, C second, and D third.

Build a cross-sheet formula step by step

Suppose Orders has product codes in A2:A20, and Products has product codes in A2:A100, names in B, and prices in C. To return a price for the code in Orders!A2, enter the formula in the intended result cell on Orders. You can type the reference directly or start the formula, click the Products tab, and select the table range while editing the formula.

  1. Confirm that the lookup values on both sheets represent the same identifiers and that the lookup column is the first column of the source range.
  2. Select the result cell on Orders, such as B2, and begin the formula with the lookup cell A2.
  3. Reference the Products range from its first lookup column through its last return column: A2:C100.
  4. Use 3 for the price column and FALSE for exact matching, then press Enter.
  5. Check the returned value against the source row. If it is correct, fill the formula down for the other order rows.

With product codes in Orders!A2:A20 and the source table in Products!A2:C100, enter this in Orders!B2: =VLOOKUP(A2,Products!$A$2:$C$100,3,FALSE). If A2 contains P-104 and Products row 8 has P-104 in column A and 12.50 in column C, the result is 12.50. The example assumes that the matching code is unique; if it appears more than once, VLOOKUP returns the first match it encounters.

Reference sheet names and lock the source range

For a sheet name without spaces, Excel uses a reference such as Products!A2:C100. For a sheet name with spaces, wrap the name in single quotation marks, as in ‘Product List’!A2:C100. Excel may add those marks automatically when you select a range on a sheet whose name needs them. A single quote inside a sheet name is represented by two single quotes in a worksheet reference.

Dollar signs keep the source range fixed when you copy a formula. Without them, filling down can shift A2:C100 to A3:C101, which may exclude the first record and include an unintended blank row. In the example, A2 is deliberately relative so it changes to A3, A4, and so on, while $A$2:$C$100 stays put. In Windows Excel, F4 cycles through reference styles while editing a cell reference; on a Mac, the equivalent shortcut is typically Command+T, though keyboard settings can affect it.

A worksheet reference is different from a separate workbook reference. Both sheets in the example belong to the same workbook, so the formula needs only the sheet name. Looking up data in a different workbook requires an external workbook reference and depends on that file’s location and availability.

Choose exact or approximate matching

Use FALSE for an exact match when looking up product codes, employee IDs, invoice numbers, or other distinct labels. Excel searches for a value equal to the lookup value. If it cannot find one, the usual result is #N/A. Exact matching does not require the lookup column to be sorted.

TRUE is for approximate matching, often used with ordered thresholds such as tax bands, shipping tiers, or grading scales. The first column of the lookup range must be sorted in ascending order. Excel finds the largest value that is less than or equal to the lookup value and returns the result from that row. For example, a rate table with lower bounds of 0, 50, and 100 can assign the band beginning at 50 to a lookup value of 72. If the data is unsorted, approximate results may be incorrect. For most everyday two-sheet lookups, FALSE is the safer choice.

One related misconception is that FALSE means the formula will find a visually similar value. It will not: an extra space, a different character, or a number stored as text can prevent an exact match. Also, VLOOKUP generally treats upper- and lowercase letters as equivalent, so it is not a case-sensitive lookup.

Common errors and practical fixes

  • #N/A: Excel did not find an exact match, or the approximate lookup could not find a suitable lower bound. Check spelling, spaces, data types, and whether the value exists in the first column of the selected range.
  • #REF!: The column index asks for a column beyond the width of table_array. Expand the range or correct the index. For A:C, the valid return-column numbers are 1, 2, and 3.
  • Wrong result: Check that the formula uses FALSE when an exact match is intended, that the lookup column is first in the range, and that duplicate lookup values are not causing VLOOKUP to return an earlier row.
  • Formula changes after filling down: Lock the lookup table range with dollar signs while leaving the row reference for the lookup cell relative.
  • Unexpected blanks or zeros: A matched source cell may itself be blank, or the chosen return column may not contain the field you intended. Inspect the source row rather than assuming every returned blank means no match.

When an exact match fails even though two values look identical, compare their underlying contents. A code may contain a leading or trailing space, or one sheet may store 123 as a number while the other stores 123 as text. Apply a consistent data type to both columns and remove unintended spaces in the source data. Changing number formatting alone changes how a value looks, not necessarily the value’s stored type.

Make formulas easier to maintain

For a short, stable list, a fixed range such as $A$2:$C$100 is straightforward. For a growing list, an Excel Table can be easier to maintain: select the source data and use Insert > Table, then give the table and its columns meaningful names. Table references can expand as rows are added, reducing the risk that a newly added record falls outside a fixed range. Keep the lookup column at the left of the table structure used by VLOOKUP.

Another option is to select a range large enough for expected growth, while still avoiding entire-column ranges if the workbook becomes slow or hard to manage. Whatever approach you use, include the correct header and data rows consistently. VLOOKUP’s col_index_num is a fixed number, so inserting or rearranging columns inside the selected range can make a formula return a different field than intended. Review formulas after restructuring a source table.

VLOOKUP alternatives and when to use them

VLOOKUP is useful when the key is in the leftmost column and the return field is to its right. XLOOKUP is more flexible: it takes separate lookup and return ranges, so the return column can be to the left or right. It also has an optional argument for a custom not-found result. XLOOKUP is available in Microsoft 365 and Excel 2021 or later; older Excel versions may not support it.

With product codes in Products!A2:A100 and prices in Products!C2:C100, this formula in Orders!B2 returns the price for Orders!A2: =XLOOKUP(A2,Products!$A$2:$A$100,Products!$C$2:$C$100). In the earlier example, its result is 12.50. INDEX and MATCH are another established alternative, including in older Excel versions, and can also look to the left, but their combined formula is less direct for many beginners. If a workbook must be opened in an older Excel release, check function compatibility before replacing VLOOKUP with a newer function.

Frequently Asked Questions

Can VLOOKUP search another sheet in the same workbook?
Yes. Put the worksheet name and an exclamation mark before the range, such as Products!$A$2:$C$100. Use single quotation marks around sheet names that contain spaces.

Why does VLOOKUP return #N/A between sheets?
Usually the exact value is absent or differs in a way that is easy to miss, such as an extra space or a number-versus-text mismatch. Confirm that the lookup column is the first column of the range and use FALSE when you need an exact match.

Can VLOOKUP return a value from a column to the left?
No. The lookup column must be the leftmost column in table_array, and VLOOKUP returns from a column to its right. Rearrange the source range or use XLOOKUP or INDEX and MATCH when the return field is on the left.

What does the 3 mean in a VLOOKUP formula?
It is the position of the return column counted from the left edge of the selected range. If the range is A:C, 3 refers to C; if it is B:D, 3 refers to D.

Does VLOOKUP work the same on Excel for Mac or the web?
The VLOOKUP formula and worksheet-reference format are the same in current Excel for Microsoft 365 on Windows, Mac, and the web. Menu layouts and some keyboard shortcuts differ by platform, but you can enter the formula directly in the cell on each version.

Related

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

More in Formulas & functions

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

Spotted an error in this guide? Let us know.