help with Index / match

[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?

 

[David Heaton]

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?

It would be helpful to see what formula you are trying to use and
which column you are trying to match

 

[Opal]

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.

 

[Roger Govier]

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.

 

[Opal]

Thank you Roger, I will give this a try.