# Index / Match Formula

#### rbahbah

##### Board Regular
Hi all,

I think I can solve this with using the index match formula. Here are the two tables that I have. I am looking for a formula to insert in table 1 that I can copy and paste (with absolute values) that pulls the total amount per month and per city from the 2nd table below.

Much appreciated!

 City February March Tokyo Formula goes here Formula goes here Milan Formula goes here Formula goes here

<tbody>
</tbody>

 Month Tokyo Milan February 20000 14500 March 21000 15000

<tbody>
</tbody>

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
If your first table has "city" in A1, then in B2:
=INDEX(\$B\$7:\$C\$8,MATCH(B\$1,\$A\$7:\$A\$8,0),MATCH(\$A2,\$B\$6:\$C\$6,0))
Drag it down and over to fill the table (or copy paste)

Alternatively, you could copy-paste special the whole second table and transpose

Last edited:
I'm not sure which would be more efficient but you could try these

=INDEX(\$B\$7:\$C\$8,MATCH(B\$1,\$A\$7:\$A\$8,0),MATCH(\$A2,\$B\$6:\$C\$6,0))

or maybe

=SUMPRODUCT((\$A\$7:\$A\$8=B\$1)*(\$B\$6:\$C\$6=\$A2),\$B\$7:\$C\$8)

 A B C 1 City February March 2 Tokyo 20000 21000 3 Milan 14500 15000 4 5 6 Month Tokyo Milan 7 February 20000 14500 8 March 21000 15000

<TBODY>
</TBODY>

Replies
1
Views
179
Replies
6
Views
104
Replies
3
Views
261
Replies
10
Views
233
Replies
7
Views
390

1,196,057
Messages
6,013,178
Members
441,752
Latest member
klfezler

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back