FormulaCraft

How to do a two-way lookup in Excel and Google Sheets

Topic:INDEX / MATCH
Excel & Google Sheets
=INDEX($B$2:$D$4,MATCH(F2,$A$2:$A$4,0),MATCH(G2,$B$1:$D$1,0))

Try it with your data

Edit the grid or formula, then run it through a real spreadsheet engine — no signup.

Sample data — click any cell to edit

Runs server-side · free · no signup

Step by step

  1. 1Set up a table with row labels in column A and column headers in row 1.
  2. 2In a separate area, put the row value to look up in one cell (e.g. F2) and the column header in another (e.g. G2).
  3. 3Enter =INDEX($B$2:$D$4,MATCH(F2,$A$2:$A$4,0),MATCH(G2,$B$1:$D$1,0)).
  4. 4The first MATCH finds the row position; the second MATCH finds the column position; INDEX returns the intersecting value.

Tips

Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.

Frequently asked

What does the 0 mean in the MATCH functions?

0 means exact match. Use 1 for approximate match on ascending-sorted data, or -1 for descending-sorted data.

Can I do a two-way lookup with VLOOKUP?

You can combine VLOOKUP with MATCH for the column index: =VLOOKUP(F2,$A$2:$D$4,MATCH(G2,$A$1:$D$1,0),0). This is less flexible than INDEX/MATCH.

More on INDEX / MATCH

See all →
Reference

INDEX / MATCH

A flexible two-function lookup that can return values to the left of the lookup column.

How-to

How to use INDEX MATCH with two criteria

Combine INDEX and MATCH with multiplication of two criteria arrays to perform a lookup that matches on two conditions simultaneously.

Error fix

Fix #N/A

Fix #N/A errors in INDEX MATCH in Excel and Google Sheets — match_type wrong, arrays different sizes, data-type mismatches, and lookup value absent.

How-to

How to do a two-dimensional lookup

Combine INDEX with two MATCH calls — one for the row and one for the column — to look up a value at the intersection of a row and column header.

Formulas used

INDEXMATCHXLOOKUPEXACTISNAHLOOKUP

Related tasks

Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.

Last reviewed: