Managing complex financial data across disconnected spreadsheets is a common administrative headache for analysts. When tracking standard funding sources like capital allocations and internal budgets, critical information often resides in siloed sheets. However, securing external grants offers a powerful financial boost to your projects, provided you can accurately track and report on compliance. Note that cross-sheet lookups require consistent unique identifiers (like Project IDs) to prevent errors. Organizations managing complex federal NSF grants successfully use Excel to automate this consolidation. Below, we will detail how to build the exact VLOOKUP and XLOOKUP formulas to retrieve this data seamlessly.

Excel Formula to Lookup Value From Another Sheet

In Excel, keeping all your data on a single worksheet is rarely practical. As your projects grow, you will naturally organize information across multiple sheets-perhaps keeping transaction histories on one tab, customer details on another, and pricing inventory on a third.

The challenge arises when you need to bring these separate pieces of data together. How do you pull a price, a customer name, or an ID from one sheet into another dynamically? The solution lies in Excel's powerful lookup formulas.

In this comprehensive guide, we will explore the three most effective ways to look up values from another sheet: the classic VLOOKUP, the modern and powerful XLOOKUP, and the highly flexible INDEX & MATCH combination. We will also cover syntax rules, troubleshooting, and best practices to ensure your spreadsheets remain accurate and efficient.

The Golden Rule of Cross-Sheet References

Before diving into the formulas, you must understand how Excel references other sheets. When referencing a cell on the same worksheet, you simply type the cell coordinate (e.g., A2). However, when referencing another sheet, you must tell Excel exactly where to look using this syntax:

SheetName!CellAddress

The exclamation mark (!) acts as a separator between the sheet name and the cell range. For example, if you want to reference cell B5 on a sheet named Inventory, you write:

Inventory!B5

What if the Sheet Name Has Spaces?

If your sheet name contains spaces, punctuation, or special characters (e.g., Product Inventory or Q1-Sales), you must enclose the sheet name in single quotation marks:

'Product Inventory'!B5

Failing to include these single quotes for sheets with spaces will cause Excel to throw a formula error. Fortunately, if you use your mouse to click and select ranges while building your formula, Excel adds these single quotes automatically.

Method 1: The Classic VLOOKUP (Vertical Lookup)

The VLOOKUP function is the traditional method for searching data in columns. It looks for a specific value in the first column of a table array and returns a value in the same row from a specified column.

VLOOKUP Syntax Across Sheets

To use VLOOKUP across sheets, the syntax is:

=VLOOKUP(lookup_value, 'SheetName'!table_array, col_index_num, [range_lookup])

• lookup_value: The value you want to search for (located in your current sheet).
• 'SheetName'!table_array: The range of cells on the other sheet containing the lookup data and the result.
• col_index_num: The column number in the table array from which to retrieve the value (starting with 1 for the leftmost column).
• [range_lookup]: Set this to FALSE (or 0) for an exact match, or TRUE (or 1) for an approximate match. In 99% of cases, you will want FALSE.

Step-by-Step VLOOKUP Example

Imagine you have two sheets:

1. Sales: This sheet contains product IDs in Column A, and you want to pull the corresponding unit prices into Column B.
2. Prices: This sheet contains a master list of Product IDs in Column A and their corresponding Prices in Column B.

To pull the price from the Prices sheet into cell B2 of your Sales sheet, enter the following formula in Sales!B2:

=VLOOKUP(A2, Prices!$A$2:$B$100, 2, FALSE)

Why the dollar signs ($)? We write $A$2:$B$100 to lock the range as an absolute reference. This ensures that when you drag the formula down to fill other rows, the lookup range remains fixed on the master list.

Method 2: The Modern XLOOKUP (Recommended)

If you are using Office 365, Microsoft 365, or Excel 2021 and newer, XLOOKUP is the superior tool. It overcomes almost all of VLOOKUP's limitations: it can look to the left, doesn't require counting column numbers, and defaults to an exact match.

XLOOKUP Syntax Across Sheets

=XLOOKUP(lookup_value, 'SheetName'!lookup_array, 'SheetName'!return_array, [if_not_found], [match_mode])

• lookup_value: The cell containing the value you want to look up in your current sheet.
• 'SheetName'!lookup_array: The single column on the other sheet where Excel should search for the lookup value.
• 'SheetName'!return_array: The single column on the other sheet from which Excel should pull the result.
• [if_not_found]: (Optional) Text or value to display if no match is found (e.g., "Not Found").

Step-by-Step XLOOKUP Example

Using the same scenario (fetching prices from the Prices sheet to the Sales sheet), the XLOOKUP formula in cell B2 would look like this:

=XLOOKUP(A2, Prices!$A$2:$A$100, Prices!$B$2:$B$100, "Price Missing")

Why XLOOKUP is Better:

• No column counting: You don't have to count columns to find the lookup value. If you insert a column between A and B on the Prices sheet later, XLOOKUP won't break, whereas VLOOKUP will.
• Leftward lookups: If the price was in Column A and the Product ID was in Column B on your Prices sheet, XLOOKUP handles it effortlessly. VLOOKUP cannot look to its left.
• Built-in error handling: The fourth parameter ("Price Missing") replaces the need to wrap your formula in an extra IFERROR function.

Method 3: INDEX & MATCH (The Traditional Alternative)

If you are using an older version of Excel (Excel 2019 or earlier) and need to perform advanced lookups (such as looking to the left or using dynamic columns), the INDEX and MATCH combination is your best choice.

Instead of one heavy function, this method combines two. MATCH finds the row number of your target item, and INDEX retrieves the value from that row in the target column.

INDEX & MATCH Syntax Across Sheets

=INDEX('SheetName'!return_range, MATCH(lookup_value, 'SheetName'!lookup_range, 0))

Step-by-Step INDEX & MATCH Example

Let's pull prices from the Prices sheet using this formula in cell B2 of the Sales sheet:

=INDEX(Prices!$B$2:$B$100, MATCH(A2, Prices!$A$2:$A$100, 0))

How it works:

1. MATCH(A2, Prices!$A$2:$A$100, 0) searches for the Product ID in A2 within Column A of the Prices sheet. It returns a row number (for example, row 12).
2. INDEX(Prices!$B$2:$B$100, 12) goes to Column B of the Prices sheet and grabs the value at the 12th position of that designated range.

Handling Common Errors & Troubleshooting

Working across multiple sheets increases the risk of calculation errors. Here are the most common pitfalls and how to fix them:

1. The dreaded #N/A Error

This means Excel searched the target sheet but could not find the lookup value. To fix this, you can wrap your formulas in an IFERROR or IFNA wrapper:

=IFERROR(VLOOKUP(A2, Prices!$A$2:$B$100, 2, FALSE), "Not Found")

For XLOOKUP, simply use its built-in fourth argument: =XLOOKUP(A2, Prices!$A$2:$A$100, Prices!$B$2:$B$100, "Not Found").

2. The #REF! Error

This usually happens in VLOOKUP when your col_index_num is greater than the number of columns in your specified table_array. For example, if your range is Prices!A2:B100 (2 columns) but your column index is set to 3, you will get a #REF! error.

3. Data Type Mismatches

If your formula returns #N/A but you can clearly see the value on the other sheet, check your data formatting. If one sheet stores Product IDs as numbers (e.g., 101) and the other stores them as text strings (e.g., '101), Excel will not recognize them as matches. Ensure both columns are formatted identically.

Pro-Tip: Use Excel Tables for Easier Formulas

If you format your source data on the other sheet as an official Excel Table (select the data and press Ctrl + T), your formulas become much easier to write and read. Excel will use structured references instead of cell coordinates.

For example, if your inventory sheet is formatted as a table named InventoryTable, your lookup formula becomes:

=XLOOKUP(A2, InventoryTable[ProductID], InventoryTable[Price])

With structured tables, you don't have to worry about adding sheet names, single quotes, exclamation marks, or absolute dollar signs ($). Excel handles all of it automatically, and the ranges will auto-expand as you add new rows of data.

Summary: Which Formula Should You Choose?

Your choice of formula depends on your version of Excel and your specific dataset structure:

By mastering these three lookup techniques, you will be able to organize your data dynamically, link multiple datasets across tabs, and maintain clean, manageable workbooks.