Ever struggled to find the right value in a Google Sheet when both your row and column headings matter? That’s exactly where a 2D lookup comes in! A 2D lookup lets you cross-reference data using two variables — one from a row and one from a column — and fetch the result where they intersect. Whether you’re tracking sales across months for different employees or managing inventory levels by location and item, this method can save you tons of time and clicks.
Don’t worry — you don’t need to be a spreadsheet wizard to pull this off. In this guide, we’ll walk through a real-life example using simple formulas so you can confidently apply 2D lookups in your own projects.
What Is a 2D Lookup?
A 2D lookup (two-dimensional lookup) helps you find a value in a table where you know the row header and the column header. It’s like asking, “What’s the sales number for John in March?” — and having Google Sheets fetch the answer automatically.
When Would You Use a 2D Lookup?
Here’s a simple example: Imagine you’re managing a sales team. You have a table that shows each salesperson’s sales for each month. You want to create a small form where you can select the name and month, and get the sales value instantly. That’s a perfect use case for a 2D lookup.
Example Scenario: Monthly Sales Tracker
Let’s say you have this table of data in Google Sheets:
Sample Data Table:
| Salesperson | January | February | March |
|---|---|---|---|
| John | 1200 | 1350 | 1400 |
| Susan | 1500 | 1600 | 1700 |
| Mike | 1100 | 1200 | 1250 |
Goal:
We want to enter a salesperson’s name and a month in two separate cells and return their sales figure for that month.
Step-by-Step Instructions: How to Do a 2D Lookup
-
- Step 1: Set up your search inputs.
Place the search terms somewhere on your sheet. For example:
-
-
- Cell G1: Salesperson (e.g., John)
- Cell G2: Month (e.g., March)
- Step 2: Use the
INDEXandMATCHcombination formula.
-
In cell G3 (or any empty cell), enter this formula:
=INDEX(B2:D4, MATCH(G1, A2:A4, 0), MATCH(G2, B1:D1, 0))
-
- Step 3: Press Enter. Voilà!
You’ll see the sales number where your selected salesperson and selected month intersect.
What’s Happening in That Formula?
- INDEX: Returns a value from a specific row and column in a given range.
- MATCH(G1, A2:A4, 0): Finds which row the salesperson is in.
- MATCH(G2, B1:D1, 0): Finds which column the month is in.
Key Benefits of 2D Lookup
- Fast retrieval of values from large tables
- No need to manually scan rows and columns
- Flexible for dashboards and reports
- Works dynamically as you change input values
Pro Tips
- Always make sure your headers (row and column) are spelled exactly like your lookup values.
- Use
DATA VALIDATIONto create dropdowns for selecting salesperson and month. - You can wrap the formula in
IFERROR()to handle blank or incorrect input gracefully.
Quick Cheat Sheet for Beginners
| Formula Component | What It Does |
|---|---|
| INDEX(range, row, column) | Gets the value from the range at the specified row and column |
| MATCH(value, range, 0) | Finds the position of a value in a row or column |
| IFERROR(value, “message”) | Returns a custom message if the formula fails |
A 2D lookup in Google Sheets is a powerful tool once you get the hang of it. With just a single formula using INDEX and MATCH, you can dynamically search through your table using both row and column headers. This makes it incredibly useful for dashboards, reports, and any scenario where you’re dealing with grid-style data.
Give it a try with your own data and you’ll be surprised how much time you save!