Another Index Match Question

[E_DPSG]

I use to be very good at these. But a few years of no practice, I am toast.

I have a single table that contains the following Columns, Where Date is in Col A, and CountC is in Col E:

Date, Source, CountA, CountB, CountC

Results will come from either C,D, or E and I can work out those details. For the assistance needed, lets assume it is coming from E.

Date	Source	CountA	CountB	CountC
7/1/2015	VendorA	1	7	15
7/1/2015	VendorB	2	8	30
7/2/2015	VendorA	3	9	20
7/2/2015	VendorB	4	10	35

<tbody>
</tbody>

This is essentially driving an alternate table to format results in a way Excel charting will like the output.
I have dats running down the Y Axis and various sources running across the X Axis.

How can I return the values Column E that matches the date and sources in column A and B.

Resulting Table:

Date	VendorA	VendorB
7/1/2015	15	30
7/2/2015	20	35

<tbody>
</tbody>

Thanks ~ EJ

 

[bschwartz]

Hi there,

You can use a PivotTable, by clicking anywhere in the table, going to Insert --> PivotTable --> Ok.

1) Then when selected anywhere in the PivotTable, go to the Design tab at the top, go to Subtotals --> Do not show subtotals. Go to Grand Totals --> Off for columns and rows. Go to Report Layout --> Show in Tabular form.
2) With a cell in the PivotTable still highlighted go to to the field list at the left, and drag and drop Date into Row Labels, Source into Column labels, and CountC into Values.

 

[E_DPSG]

Yeah - dont really care to use pivot tables.

 

[chicagocomputerclasses]

This is an array calculation, so use ctrl-shift-enter to apply

=INDEX($E$2:$E$5,MATCH(1,--($K2=$A$2:$A$5)*--(L$1=$B$2:$B$5),0))

$K2 is the date inside crosstab we are looking for on the left
L$1 is the vendor inside crosstab we are looking for on top

 

[bschwartz]

Yeah - dont really care to use pivot tables.

Reason being?

 

[E_DPSG]

This is an array calculation, so use ctrl-shift-enter to apply

=INDEX($E$2:$E$5,MATCH(1,--($K2=$A$2:$A$5)*--(L$1=$B$2:$B$5),0))

$K2 is the date inside crosstab we are looking for on the left
L$1 is the vendor inside crosstab we are looking for on top

Perfect! This is what I remember doing - just could not string it together. Much Appreciated.