1 Attachment(s)

Find MAX value and return corresponding value in column A

Hi guys,

I have a big pivot table and what I need is the following:

For each value in column A I need to retrieve the value in row 4 that has the MAX value for that row. E.g. for row 5 the formula would scan the entire row, find the MAX value and derive the respective column and give me for that column the value in row 4 (= the output I need). I just can't wrap my head around how to do this. Could one of you guys help?

I only got a screenshot as my sample file is too big to upload here (1.8MB).

Regards,

Re: Find MAX value and return corresponding value in column A

I'm sorry, the title of the thread is off.

Re: Find MAX value and return corresponding value in column A

Does anyone have an idea?

1 Attachment(s)

Re: Find MAX value and return corresponding value in column A

Could you do a max outside the pivot table? If you have the pivot returning the max for each grouping (rows and columns) then you have the max for each column within the pivot. Insert a column next to your pivot on the right (make a new column A) and do a max formula where you reference the cells and not click on the pivot.

I have attached an example. I hope this helps. It would help to reference what you want in a specific cell from what range.

Re: Find MAX value and return corresponding value in column A

This is great! Just a question; how would I retrieve the correct grouping? You have the right ones, but they are values. Is there a way to do this automatically?

Thank you for your effort already!

Re: Find MAX value and return corresponding value in column A

Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

Re: Find MAX value and return corresponding value in column A

I'm not sure what you mean. I did a quick example. I assume you have a spreadsheet with the above pictured pivot table. Does the Pivot table go all the way to column xfd? If not insert a column in a and move the pivot table over 1 column. Then use the formula from the first cell of data to the last cell not including any grand total. When you change your data set and refresh the pivot table the max will recalculate. Are you trying to move the max value somewhere else based on the value in column A in the picture? That is a separate requirement that is easily done. That can be a lookup or index/match formula.

Re: Find MAX value and return corresponding value in column A

Hi mstwntd,

What I mean is that I do not need the max as a value, but I need the max as a means to find the value I need.. I need to find the max in each row, and find the corresponding value in your row 4 (1 / 2 / 3 / 4 / 5 / 6).

So for your example:

For row 'a' it would retrieve value '1', as the max 55 is in column '1'

For row 'b' it would retrieve value '1 (or 2)', as the max 77 is in column '1 and 2'

For row 'c' it would retrieve none

For row 'd' it would retrieve value '4' as the max 33 is in column 3

etc.

Do you have any idea?

Re: Find MAX value and return corresponding value in column A

Try this (the double comma is intentional):

=INDEX(K$4:P$4,,MATCH(MAX(K5:P5),K5:P5,0))

Re: Find MAX value and return corresponding value in column A

The lack of an example makes this a bit of a guess...

An array formula, copied down:

=IFERROR(MATCH(1/(1/MAX(IF(K5:P5>0,K5:P5))),K5:P5,0),"")

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the **FIRST CELL ONLY** by pressing __CTRL+SHIFT+ENTER__ to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

Re: Find MAX value and return corresponding value in column A

THANK YOU AliGW! Works like a charm.

Re: Find MAX value and return corresponding value in column A

You're welcome! :)

If that takes care of your original question, please select *Thread Tools* from the menu link above and mark this thread as SOLVED. Thanks.

Re: Find MAX value and return corresponding value in column A

Does it work??? Doesn't it return a 3 when it should return a blank in the 3rd row??

Re: Find MAX value and return corresponding value in column A

Why should it return a blank? The maximum value in the row is a zero. We are using the attachment in post #4.