Find MAX value and return corresponding value in column A

**Slic3d** · 10-13-2017, 08:30 AM

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,

**Slic3d** · 10-13-2017, 08:35 AM

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

**Slic3d** · 10-13-2017, 10:48 AM

Does anyone have an idea?

**mstwntd** · 10-13-2017, 12:52 PM

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.

**Slic3d** · 10-15-2017, 08:54 AM

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!

**AliGW** · 10-15-2017, 08:59 AM

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.

**mstwntd** · 10-16-2017, 11:44 AM

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.

**Slic3d** · 10-17-2017, 03:10 AM

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?

**AliGW** · 10-17-2017, 03:14 AM

Try this (the double comma is intentional):

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

**Glenn Kennedy** · 10-17-2017, 03:18 AM

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...

**Slic3d** · 10-17-2017, 03:29 AM

THANK YOU AliGW! Works like a charm.

**AliGW** · 10-17-2017, 03:30 AM

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.

**Glenn Kennedy** · 10-17-2017, 03:31 AM

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

**AliGW** · 10-17-2017, 03:44 AM

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

Find MAX value and return corresponding value in column A

LinkBack

Thread Tools

Rate This Thread

Display

Find MAX value and return corresponding value in column A

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

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

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Find top 5 highest values in row from every third column and return column header

Search and find the value of a cell of 1st column in the 2nd column than return that value

[SOLVED] Find first non-zero value in a column and return adjacent value in column to the left?

Find the same number in the column and then return text from another column"

[SOLVED] Find last occurrence of text in a column and return value in next column

Find the last letter in a column and return a value in the same row but a different column

Formula to find all matches in column A and return cells from column C

Bookmarks

Bookmarks

Posting Permissions