INDEX with Double MATCH in Google Sheets (Two-Way Lookup)

INDEX with double MATCH is the classic two-way lookup in Google Sheets. One MATCH finds the right row, a second MATCH finds the right column, and INDEX returns the value where they meet. Unlike VLOOKUP, it can look to the left, it does not break when you move columns, and it lets you pull several fields in any order with one formula.

This tutorial uses the free practice file built on a small call-centre table (Emp Name, Login Hours, AHT, Call Handled and EMP ID). You will start with plain INDEX, add one MATCH, and then build the double MATCH formula step by step, with the exact results you should see.

🎥 Watch the Video Tutorial

Make a Copy of the Free Practice File

The Sample Data

The data sits in A1:E11: a header row and ten employees.

The key you search by, EMP ID, is in the last column, to the right of everything you want to return. VLOOKUP cannot handle that layout without rearranging the data; INDEX and MATCH can.

Syntax of INDEX and MATCH

=INDEX(reference, [row], [column])
=MATCH(search_key, range, [search_type])

Step 1: INDEX on Its Own

In the practice file, cell A15 (under the label “Index Function”) contains:

=INDEX(A1:E11,4,2)

Row 4 of A1:E11 is Raj (the header counts as row 1) and column 2 is Login Hours, so the result is 7.3. The problem: the 4 and the 2 are typed by hand. If the data is re-sorted or a column is inserted, the answer silently becomes wrong. MATCH fixes that.

Step 2: INDEX with One MATCH

The “Index Function with match” section looks up EMP IDs 10003, 10005 and 10007. MATCH finds which row each ID is on:

=MATCH(10003,$E$1:$E$11,0)

This returns 4, because 10003 is in the fourth cell of E1:E11. Put that inside INDEX to return the employee name from column A, even though column A is to the left of the ID:

=INDEX($A$1:$A$11,MATCH(A19,$E$1:$E$11,0))

With 10003 in A19 the result is Raj. You can return Login Hours, AHT and Call Handled the same way by changing the first range to column B, C or D. That works, but you need a different formula in each column. The double MATCH removes that.

Step 3: INDEX with Double MATCH (Two-Way Lookup)

In the “Index Function with double match” section, the result table has the headers in a different order from the source: EMP ID, Emp Name, AHT, Login Hours, Call Handled (row 25), with the IDs 10003, 10005 and 10007 in A26:A28. One formula fills the whole block. In B26 enter:

=INDEX($A$1:$E$11, MATCH($A26,$E$1:$E$11,0), MATCH(B$25,$A$1:$E$1,0))

Then copy it across to E26 and down to row 28.

• MATCH($A26,$E$1:$E$11,0) finds the row of the EMP ID. The $ before A keeps it pointing at the ID column when you copy across.
• MATCH(B$25,$A$1:$E$1,0) finds the column of the header. The $ before 25 keeps it pointing at the header row when you copy down.
• INDEX returns the value at that row and column of A1:E11.

Worked result

Check one cell by hand: for Raj’s AHT, the row MATCH returns 4 (10003 is the 4th cell of E1:E11) and the column MATCH returns 3 (“AHT” is the 3rd header in A1:E1). Row 4, column 3 of A1:E11 is 237. Notice that AHT and Login Hours appear in swapped order compared with the source, and the formula still returns the right numbers, because it reads the header text instead of a fixed column number.

Practical Variations

• Look up by name instead of ID: =INDEX($A$1:$E$11, MATCH("Rose",$A$1:$A$11,0), MATCH("Call Handled",$A$1:$E$1,0)) returns 224.
• Dropdown-driven lookup: add dropdowns with Insert > Dropdown for EMP ID and metric, and point the two MATCH functions at those cells to build a mini lookup panel.
• Friendly message when nothing is found: =IFERROR(INDEX(...), "Not found").
• Find the top performer: =INDEX($A$2:$A$11, MATCH(MAX($D$2:$D$11),$D$2:$D$11,0)) returns Jack, who handled the most calls (363).
• Two-condition row match: MATCH(1,(range1=value1)*(range2=value2),0) wrapped in ARRAYFORMULA finds a row that meets both conditions.

Common Errors and Fixes

Tips for Reliable Lookups

• Keep the MATCH ranges starting on the same row and column as the INDEX range, otherwise the positions are off by one.
• Use unique keys such as EMP ID rather than names, which can repeat.
• Keep header text identical in the source and the report, since the column MATCH compares text exactly.
• Google Sheets also supports XLOOKUP, which can do left lookups too; INDEX/MATCH remains the most flexible two-way option.

Lookups like this are at the heart of most reporting work. If you want structured practice with INDEX, MATCH, XLOOKUP and many more, the Excel AI Mastery: 100 Formulas course on NextGenTemplates Academy covers them lesson by lesson, and the same logic works in Google Sheets.

Related Lookup Tutorials

• INDEX function in Google Sheets: easy guide
• MATCH function in Google Sheets with a step-by-step example
• INDEX with MATCH in Google Sheets: a simple example
• VLOOKUP in Google Sheets with a real-life example
• XLOOKUP in Google Sheets: 5 real use cases

Frequently Asked Questions

What is a double MATCH in Google Sheets?

It is an INDEX formula with two MATCH functions: one supplies the row number and the other supplies the column number, so the lookup adjusts to both the record and the field you ask for.

Why use INDEX MATCH instead of VLOOKUP?

INDEX MATCH can return values to the left of the search column, does not break when columns are inserted, and with double MATCH can return any field by its header name.

Why does my INDEX MATCH return #N/A?

MATCH did not find the search value. Common causes are extra spaces, an ID stored as text in one place and as a number in another, or a header spelled differently.

Does the column order in my report have to match the source?

No. Because the column MATCH looks up the header text, you can order the report columns any way you like, as in the example where AHT comes before Login Hours.

Can INDEX MATCH return a whole row?

Yes. Use 0 for the column argument, for example =INDEX($A$1:$E$11, MATCH(10003,$E$1:$E$11,0), 0), and the whole row for Raj spills across the cells.

Wrapping Up

INDEX with double MATCH gives you one formula that finds any record by its key and any field by its header, in any column order. Make a copy of the practice file, change the EMP IDs in the result table or swap the header order, and see how the values follow automatically.

For a ready-made view of call volume, response times, first call resolution and agent performance, take a look at the Call Center KPI Dashboard in Google Sheets.

Get the Call Center KPI Dashboard

📅 Last updated: October 2026