Ever wished you could pull data from multiple sheets in one go — as if Google Sheets could search across tabs just like magic? That’s exactly what a 3D Lookup helps you do. It lets you look up a value based on a specific row and column, not just in one sheet, but across multiple sheets dynamically.
Whether you’re managing monthly reports, tracking sales across regions, or compiling department budgets, this technique can save hours of manual work. The best part? You don’t need to be a spreadsheet expert to use it. In this guide, you’ll learn how to do a 3D Lookup in Google Sheets step-by-step, with a real example and simple formulas anyone can follow.
What Is a 3D Lookup in Google Sheets?
A 3D Lookup is a method for looking up data across multiple sheets based on a combination of row and column criteria. Unlike the typical VLOOKUP or INDEX/MATCH that search within a single sheet, 3D lookups pull from several tabs at once — like January, February, March, and so on.
Real-Life Scenario: Monthly Sales Data Across Sheets
Let’s say you’re managing a retail business. Each month has its own sheet in Google Sheets — “Jan”, “Feb”, “Mar” — and each sheet looks like this:
Sample Monthly Sheet (“Jan”):
| Product | Store A | Store B |
|---|---|---|
| Apples | 120 | 150 |
| Bananas | 200 | 180 |
Now you want to create a summary sheet where you enter the month, product, and store — and Google Sheets returns the sales figure from the correct sheet!
Step-by-Step Guide: How to Do a 3D Lookup in Google Sheets
-
- Step 1: Set up your input cells in the Summary sheet.
- Cell B1: Month (e.g., Jan, Feb, Mar)
- Cell B2: Product (e.g., Apples, Bananas)
- Cell B3: Store (e.g., Store A, Store B)
- Step 2: Use this formula in Cell B5 (or any output cell):
- Step 1: Set up your input cells in the Summary sheet.
=INDEX(INDIRECT(B1 & "!B2:C3"), MATCH(B2, INDIRECT(B1 & "!A2:A3"), 0), MATCH(B3, INDIRECT(B1 & "!B1:C1"), 0))
- Step 3: Press Enter to get your result.
This formula pulls the sales value from the correct sheet, based on your chosen month, product, and store.
How the Formula Works
INDIRECT(B1 & "!B2:C3"): Dynamically builds the range from the sheet named in B1.MATCH(B2, INDIRECT(B1 & "!A2:A3"), 0): Finds the row for the product.MATCH(B3, INDIRECT(B1 & "!B1:C1"), 0): Finds the column for the store.INDEX(...): Retrieves the value at the intersection.
Real Example: Let’s Say You Have These Inputs
| Cell | Value |
|---|---|
| B1 | Feb |
| B2 | Bananas |
| B3 | Store B |
If the sheet Feb has the same layout as “Jan”, and the sales value for Bananas at Store B is 190, the formula will return 190.
Key Benefits of 3D Lookup
- Dynamic Lookup: Retrieve data from different sheets without manually switching tabs.
- Organized Data: Keep each month or department on a separate sheet and still access it from one place.
- Easy Maintenance: Just add new sheets — the lookup formula keeps working.
Pro Tips
- Use consistent layouts across all sheets (same headers and cell positions).
- Create drop-downs using Data Validation for months, products, and store names to avoid typos.
- Wrap the formula in
IFERROR(..., "Not Found")to avoid errors when inputs are invalid. - Use named ranges if your table sizes vary across sheets.
Quick Cheat Sheet for Beginners
| Function | Purpose |
|---|---|
INDIRECT() |
Builds a reference to a range or sheet using text. |
INDEX(range, row, column) |
Returns the value at a given row and column. |
MATCH(value, range, 0) |
Finds the position of a value within a range. |
IFERROR(value, fallback) |
Prevents errors by showing a custom fallback text. |
A 3D lookup in Google Sheets lets you pull data from multiple tabs using a single smart formula. Once you’ve mastered it, you’ll save hours hunting through tabs and typing formulas over and over. All you need is consistent sheet structure and a good handle on INDEX, MATCH, and INDIRECT.
Try it out with your own monthly data — and impress your team with your spreadsheet superpowers!