Index Match question

[odonovanc]

I suspect I need to use a different function but I am trying to lookup data and in one tab the city name is in one column, but the tab with the data the city names are spread out over manmy columns.

[TABLE="width: 278"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD]Location[/TD]
[TD][/TD]
[TD]Revenue[/TD]
[TD]Expenses[/TD]
[/TR]
[TR]
[TD]Boston[/TD]
[TD][/TD]
[TD="align: right"]150000[/TD]
[TD="align: right"]168000[/TD]
[/TR]
[TR]
[TD]Chicago[/TD]
[TD][/TD]
[TD="align: right"]99000[/TD]
[TD="align: right"]107910[/TD]
[/TR]
[TR]
[TD]Dallas[/TD]
[TD][/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD]Denver[/TD]
[TD][/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD]Houston[/TD]
[TD][/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD]Nw York[/TD]
[TD][/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD]San Francisco[/TD]
[TD][/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF!

[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 624"]
<colgroup><col><col span="7"></colgroup><tbody>[TR]
[TD][/TD]
[TD]Boston[/TD]
[TD]Chicago[/TD]
[TD]Dallas[/TD]
[TD]Denver[/TD]
[TD]Houston[/TD]
[TD]Nw York[/TD]
[TD]San Francisco[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Revenue[/TD]
[TD] 150,000[/TD]
[TD] 168,000[/TD]
[TD] 188,160[/TD]
[TD] 210,739[/TD]
[TD] 236,028[/TD]
[TD] 264,351[/TD]
[TD] 296,073[/TD]
[/TR]
[TR]
[TD]Expenses[/TD]
[TD] 99,000[/TD]
[TD] 107,910[/TD]
[TD] 117,622[/TD]
[TD] 128,208[/TD]
[TD] 139,747[/TD]
[TD] 152,324[/TD]
[TD] 166,033[/TD]
[/TR]
</tbody>[/TABLE]


I am trying this formula to no success:
=INDEX(Sample!$C$4:$I$5,MATCH(A2,Sample!$C$2:$I$2,0),MATCH(C$1,Sample!$B$4:$B$5,0))


Can someone help

 

[AhoyNC]

Looks like all you need to do is reverse your MATCH functions.

Excel Workbook
	A	B	C
1	Location	Revenue	Expenses
2	Boston	150000	99000
3	Chicago	168000	107910
4	Dallas	188160	117622
5	Denver	210739	128208
6	Houston	236028	139747
7	Nw York	264351	152324
8	San Francisco	296073	166033
Sheet2

Excel Workbook
	A	B	C	D	E	F	G	H	I
1
2			Boston	Chicago	Dallas	Denver	Houston	Nw York	San Francisco
3
4	Revenue		150,000	168,000	188,160	210,739	236,028	264,351	296,073
5	Expenses		99,000	107,910	117,622	128,208	139,747	152,324	166,033
Sample

 

[odonovanc]

That got it. Thanks.

 

[AhoyNC]

You're welcome. Thanks for the feedback.