Index / Match Formula

[rbahbah]

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>

 

[bruderbell]

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

 

[Weazel]

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</SPAN>	February</SPAN>	March</SPAN>
2	Tokyo</SPAN>	20000</SPAN>	21000</SPAN>
3	Milan</SPAN>	14500</SPAN>	15000</SPAN>
4
5
6	Month</SPAN>	Tokyo</SPAN>	Milan</SPAN>
7	February</SPAN>	20000</SPAN>	14500</SPAN>
8	March</SPAN>	21000</SPAN>	15000</SPAN>

<TBODY>
</TBODY>