Return a value from an intersection.

[Nemo74]

Hello all, ok here is what is kicking my butt:


NYC La. Dc. Tampa. Madison
Jim. 0. 4. 0. 0. 0
Joe. 4. 1. 0. 0. 0
Bob. 3. 15. 1. 0. 0
Ann. 0. 0. 0. 0. 0
Margret. 10. 3. 1. 0. 1


I need a formula to return the value at each intersection. The formula will be in another sheet. The other sheet is like this
City name. Value
NYC. Jim. ?
NYC. Joe ?
NYC. Bob. ?
NYC. Ann ?
NYC. Margret. ?
Repeats for each city.


The goal is to turn the first chart into a pivot chart that is able to select city and name via a slicer.


Big fun!


Thanks in advance!

 

[Aladin Akyurek]

Sheet1, A:F, the data...

	NYC	La	Dc	Tampa	Madison
Jim	0	4	0	0	0
Joe	4	1	0	0	0
Bob	3	15	1	0	0
Ann	0	0	0	0	0
Margret	10	3	1	0	1

<colgroup><col style="width: 48pt;" span="6" width="64"> <tbody>
</tbody>

Sheet2, A:C, the processing...

C2, just enter and copy down:

=INDEX(Sheet1!$B$2:$F$6,MATCH($B2,Sheet1!$A$2:$A$6,0),MATCH($A2,Sheet1!$B$1:$F$1,0))

 

[Nemo74]

Thanks Aladin,
I was using the same formula set up, but I don't know how what I was doing wrong.

At least I was on the right track. Thanks for setting me stright!

 

[Aladin Akyurek]

Thanks Aladin,
I was using the same formula set up, but I don't know how what I was doing wrong.

At least I was on the right track. Thanks for setting me stright!

Glad to help.