Find a rate by product and region in Excel with INDEX and MATCH
The hourly rate for Cleaning depends on the region, so neither product nor region alone can pick a price. Join the two into one key, and a single exact-match lookup finds the rate for the pair.
Written 12 November 2026
Result
| Painting in the South, per hour | $33.00 |
| Cleaning in the East, per hour | $30.00 |
| Six hours of Painting in the South | $198.00 |
Download the spreadsheet: rate-by-product-and-region.xlsx (5,530 bytes)
It contains the nine-row rate table with its key column, and both lookups — Painting in the South worked in steps, and Cleaning in the East in one formula.
1What you need
- Excel, Google Sheets, or LibreOffice. INDEX and MATCH work the same in all three for this tutorial.
- The file above, or a blank workbook if you want to type the formulas in yourself.
2One condition is not enough
Cleaning costs $32 an hour in the North, $29 in the South, and $30 in the East. A lookup on product alone has three right answers, and a lookup on region alone has three more. The rate belongs to the pair: product and region together. The table holds all nine pairs:
| Product | Region | Rate per hour | Key |
|---|---|---|---|
| Cleaning | North | $32.00 | Cleaning|North |
| Cleaning | South | $29.00 | Cleaning|South |
| Cleaning | East | $30.00 | Cleaning|East |
| Gardening | North | $28.00 | Gardening|North |
| Gardening | South | $26.00 | Gardening|South |
| Gardening | East | $27.00 | Gardening|East |
| Painting | North | $36.00 | Painting|North |
| Painting | South | $33.00 | Painting|South |
| Painting | East | $35.00 | Painting|East |
3Build the key column
Column D joins the two conditions into one text key per row:
D3 =A3&"|"&B3The & operator joins text. The “|” between the two values is not decoration: without a separator, two different pairs can join into the same string. A product ending in one letter and a region starting with it would collide with a longer product name. With the separator in place, every pair in the table has exactly one key, and one key names exactly one row.
4Build the same key for the lookup
The job being priced is Painting in the South, typed into G2 and G3. Its key is built with the same recipe:
G4 =G2&"|"&G3G4 holds “Painting|South”, character for character the same text as the key on the Painting–South row of the table. That sameness is the whole mechanism: the lookup in the next step is an ordinary exact match, just on a joined key.
5MATCH the key, INDEX the rate
G5 =MATCH(G4,D3:D11,0)
G6 =INDEX(C3:C11,G5)MATCH finds “Painting|South” at position 8 in the key column, and INDEX returns the rate at that position in the rate column: $33.00 an hour. This is the standard INDEX and MATCH pattern (Look up values with VLOOKUP, INDEX, or MATCH — Microsoft Support), with the key column doing the two-condition work.
6Price the job
G8 =G6*G7With 6 hours in G7, the job costs $198.00. The rate cell stays a rate and the cost cell stays a product of rate and hours, so quoting a longer job is a change to one input.
7The same lookup in one formula
Once the steps are clear, MATCH can nest inside INDEX and the position cell goes away. The file's second lookup prices Cleaning in the East:
G12 =G10&"|"&G11
G13 =INDEX(C3:C11,MATCH(G12,D3:D11,0))Result: $30.00 an hour. Same key, same match, one less cell.
Traps
Matching the two columns separately. A MATCH on the product column finds the first Cleaning row; a MATCH on the region column finds the first South row. Those are different rows, and an INDEX fed one position while you think about the other returns a real rate, for a pair nobody asked about.
A key column that stops early. The key formula has to be filled beside every row of the table. A table extended by two rows whose keys were never filled leaves those pairs with no key to match, and the lookup returns #N/A for a rate that is sitting in the table.
Building the two keys differently. A separator on one side and none on the other, or a space typed into one lookup input, makes two keys that look alike and match nothing. Build both keys with the same formula shape, and let the lookup cells hold plain values.
Sources
- Look up values with VLOOKUP, INDEX, or MATCH — Microsoft Support — the INDEX and MATCH pattern this two-criteria lookup is built from.