5 Ways to Lookup Data Across Sheets in Excel

Manipulating data across multiple sheets in Microsoft Excel can significantly enhance your productivity, making it easier to organize and analyze information. Whether you are working with financial models, inventory lists, or any datasets that span across several sheets, mastering the art of data lookup can turn you into an Excel pro. Here are five powerful ways to lookup data across sheets in Excel, designed to make your data manipulation tasks seamless and efficient.

1. VLOOKUP with Indirect for Dynamic References

The VLOOKUP function is a staple in Excel for vertical lookup, but it becomes even more powerful when paired with INDIRECT. This combination allows for dynamic references to different sheets.

• Step 1: Set up your VLOOKUP function in the usual way, but instead of directly referencing the sheet name, use INDIRECT to construct the reference.
• Step 2: Example Formula: =VLOOKUP(A1,INDIRECT(“‘”&B1&“’!A:C”),2,FALSE)
  • Here, A1 contains the lookup value.
  • B1 contains the name of the sheet where the data resides.
  • The formula searches for A1 in column A of the sheet named in B1, returning the value from the second column.

✏️ Note: The INDIRECT function makes your VLOOKUP formulas more flexible, allowing users to change the sheet name dynamically.

2. Index and Match for More Versatile Lookups

While VLOOKUP is excellent for column searches, INDEX combined with MATCH gives you the freedom to look up values both horizontally and vertically across sheets.

• Step 1: Use INDEX to get the cell reference of your lookup value across sheets: =INDEX(Sheet2!A:C,MATCH(A2,Sheet2!A:A,0),MATCH(B1,Sheet2!1:1,0))
• Step 2:
  • A2 is the lookup value.
  • Sheet2!A:A is the lookup column.
  • B1 determines the row in the first column to lookup.
  • The formula matches the column header from Sheet2!1:1 and returns the value at the intersection.

3. XLOOKUP for Simpler Data Retrieval

Introduced in Excel 365, XLOOKUP is a versatile replacement for VLOOKUP, HLOOKUP, and even INDEX & MATCH. Here’s how to use it:

• Step 1: Simply construct your XLOOKUP: =XLOOKUP(A1,Sheet2!A:A,Sheet2!B:B)
  • This formula looks for A1 in column A of Sheet2, returning the corresponding value from column B.
• Step 2: You can also search across multiple sheets: =XLOOKUP(A1,{Sheet1!A:A,Sheet2!A:A,Sheet3!A:A},{Sheet1!B:B,Sheet2!B:B,Sheet3!B:B})
  • This searches across three sheets for A1 and returns the matched value from their respective B columns.

4. Power Query for Advanced Data Extraction

Excel’s Power Query tool lets you extract, transform, and load data from multiple sheets into a single, unified table, which is particularly useful for complex data operations:

• Step 1: Go to Data > Get & Transform Data.
• Step 2: Use the From Other Sources option to select a blank query, then manually load sheets.
• Step 3: After loading, combine the data using Merge or Append queries to create your result set.

✏️ Note: Power Query is excellent for large datasets or when you need to perform regular updates to your data.

5. Using a Table Structure for Dynamic Data Extraction

Creating a Table in Excel helps manage data dynamically. Here’s how to use tables for lookups:

• Step 1: Convert your data range into a table using Ctrl + T.
• Step 2: Use Excel functions like FILTERXML or FILTER for advanced lookups:
  • =FILTER(Suppliers[#All],Suppliers[Product] = A1) will return all rows where the product matches A1.

In essence, Excel provides numerous tools for looking up data across sheets, each tailored to different scenarios:

• VLOOKUP with INDIRECT for sheet name flexibility.
• INDEX and MATCH for two-dimensional lookups.
• XLOOKUP for simple and advanced lookups.
• Power Query for data integration from multiple sources.
• Tables for dynamic data handling.

By mastering these techniques, you’ll save time, increase accuracy, and unlock the full potential of Excel for data analysis and reporting.

What are the limitations of VLOOKUP?

+

VLOOKUP requires the lookup value to be in the first column of the range being searched. It can’t look left, and it also can’t handle dynamic sheet referencing without additional functions like INDIRECT.

Can XLOOKUP look up values from multiple sheets?

+

Yes, XLOOKUP can look up values from multiple sheets when you provide an array of ranges to search, making it more versatile than VLOOKUP or HLOOKUP.

Is Power Query available in all versions of Excel?

+

No, Power Query is available in Excel 2010 and later as an add-in, but it’s built into Excel 2016 and Excel 365 by default.