Excel like a finance pro.
The functions, VBA, and shortcuts a finance operator actually uses — curated, with CFO examples. Prefer them pre-built? Grab a ready-made model.
INDEX + MATCH + MATCH
Lookups & referencesThe two-way lookup — cross a row label and a column label to pull one cell.
Difficulty
1What is it?
One MATCH finds the row, a second MATCH finds the column, and INDEX returns the cell where they meet. It's the most robust way to read a grid — the value at the intersection of a row header (say an account) and a column header (say a month) — and unlike HLOOKUP/VLOOKUP it doesn't break when rows or columns are inserted.
2What it looks like
INDEX(grid, MATCH(row_val, row_headers, 0), MATCH(col_val, col_headers, 0))- grid
- The block of values to return from — no headers inside it.
- MATCH(row_val, …)
- Finds which row: searches the vertical header column.
- MATCH(col_val, …)
- Finds which column: searches the horizontal header row.
- 0
- Exact match for each MATCH (almost always what you want).
3When you use it
- Read a cell from a matrix by its row and column labels (account × month).
- Build a report cell that two dropdowns drive.
- Replace a fragile nested HLOOKUP/VLOOKUP with one stable formula.
4See it in action
Change the inputs — the formula and result update live. Prefer the real thing? Download the Excel file and open it in Excel.
Cross a region (down the side) and a quarter (across the top) to pull one cell — and it survives inserted rows or columns.
=INDEX(B2:D4, MATCH("East", A2:A4, 0), MATCH("Q2", B1:D1, 0))$110k| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Q1 | Q2 | Q3 | Result |
| 2 | West | $120k | $135k | $150k | $110k |
| 3 | East | $90k | $110k | $130k | |
| 4 | South | $70k | $80k | $95k |
The lime cell holds the formula — click it (or any cell) to see its contents in the bar above, just like Excel. Edit the blue cells to watch it recompute.
5Common errors
#N/AOne of the MATCHes found nothing.Fix: Use 0 for an exact match and check for text-vs-number on both headers.
#REF!A MATCH position falls outside the grid.Fix: Make the grid line up exactly with the header ranges — don't include a header row/column inside it.
6Better functions & alternatives
- XLOOKUP (nested) — In Microsoft 365, =XLOOKUP(row, …, XLOOKUP(col, …)) does a two-way lookup too.
- INDEX + MATCH — The one-dimensional version, when you only need to find a row.
Want INDEX + MATCH + MATCH already wired into a model? Wauvel's free tools download as branded, formula-driven Excel.
Learn the moves here — or let Wauvel run them on your numbers.
Get my free report →