help with Index / match

  • Thread starter Thread starter Opal
  • Start date Start date
O

Opal

I am using Excel 2003 and I'm trying to create a formula to
look up a value in a Pivot table. The pivot table results would
be:

Date DB CVT ACD PKT
5/21 0.1 2.1 1.2
5/22 .01 1
5/23 1.1 .02
5/24 2.1 .02
5/25 1.2 1.2

I want to show in the formula result the values in column DB
I thought I could use a combination of Index and match but I can't
quite get it right.

Basically the pivot table updates weekly and the values I want
may not always be in the DB column, they may in another
column. Can I achieve this with these functions?
 
I am using Excel 2003 and I'm trying to create a formula to
look up a value in a Pivot table.  The pivot table results would
be:

Date     DB   CVT   ACD  PKT
5/21      0.1              2.1   1.2
5/22                .01     1
5/23      1.1                      .02
5/24      2.1     .02
5/25      1.2                1.2

I want to show in the formula result the values in column DB
I thought I could use a combination of Index and match but I can't
quite get it right.

Basically the pivot table updates weekly and the values I want
may not always be in the DB column, they may in another
column.  Can I achieve this with these functions?
Click to expand...

It would be helpful to see what formula you are trying to use and
which column you are trying to match
 
That's just it, I'm not sure how to achieve what I
want. I don't have a formula right now....

Its difficult to explain but the pivot table would
look something like what I copied in my original post.
I need to make a chart out of the most frequent data
and in this case it would be all the data under
"DB" Howvever the next time I refresh the pivot
the data may be under "PKT" and so I need
to be able to show that data in a separate
range of cells for the chart source.
 
Hi

If I understand you correctly, then you want the column of data that has the
most values in it.
If so, outside the PT, in row 1 (insert a row if your page field comes in
row 1) set up a count
In cell B1
=COUNT(B5:B1000)
Copy across through columns C:E

To the right of the PT (I used cell I5) enter
=INDEX(B5:E1000,,MATCH(MAX($B$1:$E$1),$B$1:$E$1,0))
Copy down as far as required.

Base your chart on column I data.
 
Back
Top