Match(Max where a cell =

[dbarbella]

Hi All,
I'm close to having a formula working

=INDEX('Pivot Table - Monthly'!$B$2:$O$2,(MATCH(MAX('Pivot Table - Monthly'!$B$3:$O$3),'Pivot Table - Monthly'!$B$3:$O$3,0)))

This returns a Value in Row 2
based on the position number of the highest value in row 3
that is working fine.

What I need to do, and can't figure out, is to:
Return the value in Row 2
based on the highest value
in the row where 'Pivot Table - Monthly'!$B$2:$O$999 = $C3

I feel I have to take the above formula and nest it inside another Index, or Index(Match statement.
but I find myself pretty lost at this point.

Any advice would be extremely appreciated
Thanks
-Dave

 

[Aladin Akyurek]

Where C3 must occur -- in 'Pivot Table - Monthly'!$B$2:$B$999?

 

[dbarbella]

Yes, In column C I have many rows of ID numbers that I need to match up with the match row in column B of the Pivot Table - Monthly tab.

I am basically checking each row 3 through 100 (Just for an example, here’s more), for the largest value across columns B through O (well technically row C, Since B holds the ID, Inhad that wrong in my formula). When it finds the largest amount I need to capture the array position and grab the text in row 2 (same column found in the max value).

I figured that was confusing to explain

My formula works, but you can see I’m manually looking through row 3 in the Match(.

I need to actually index my ID column in the pivot tab, and find the correct matching row by grabbing the Id in column C (then I copy and paste the formula down so it grabs each Id in $C# repectively

Hope that made sense.
It may be easy to say
I need to get rid of the hard-coded Match(max and use column C to lookup the right row on the pivot tab (and the rest works fine)

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=INDEX('Pivot Table - Monthly'!$C$2:$O$2,MATCH(MAX(INDEX(Pivot Table - Monthly'!$C$3:$O$999,0,MATCH($C3,'Pivot Table - Monthly'!$B$3:$B$999,0))),INDEX(Pivot Table - Monthly'!$C$3:$O$999,0,MATCH($C3,'Pivot Table - Monthly'!$B$3:$B$999,0)),0))

Does this help?

 

[dbarbella]

Control+shift+enter, not just enter:

=INDEX('Pivot Table - Monthly'!$C$2:$O$2,MATCH(MAX(INDEX(Pivot Table - Monthly'!$C$3:$O$999,0,MATCH($C3,'Pivot Table - Monthly'!$B$3:$B$999,0))),INDEX(Pivot Table - Monthly'!$C$3:$O$999,0,MATCH($C3,'Pivot Table - Monthly'!$B$3:$B$999,0)),0))

Does this help?

Thank you so much,
The formula returned a Parse Error. Although I see Google Sheets changed it slightly with adding ArrayFormula to the front after the CTRL/SHIFT/ENTER

=ArrayFormula(INDEX('Pivot Table - Monthly'!$C$2:$O$2,MATCH(MAX(INDEX(Pivot Table - Monthly'!$C$3:$O$999,0,MATCH($C3,'Pivot Table - Monthly'!$B$3:$B$999,0))),INDEX(Pivot Table - Monthly'!$C$3:$O$999,0,MATCH($C3,'Pivot Table - Monthly'!$B$3:$B$999,0)),0))))

 

[dbarbella]

Thank you so much,
The formula returned a Parse Error. Although I see Google Sheets changed it slightly with adding ArrayFormula to the front after the CTRL/SHIFT/ENTER

=ArrayFormula(INDEX('Pivot Table - Monthly'!$C$2:$O$2,MATCH(MAX(INDEX(PivotTable-Monthly'!$C$3:$O$999,0,MATCH($C3,'PivotTable-Monthly'!$B$3:$B$999,0))),INDEX(Pivot Table - Monthly'!$C$3:$O$999,0,MATCH($C3,'Pivot Table - Monthly'!$B$3:$B$999,0)),0))))

I played with the formula a little by adding apostrophes in front of a couple spots referencing the Pivot Table sheet, and removed a couple of parenthesis when an error came up about that. The result feels like the formula is now running but returns an NUM error that "Function INDEX parameter 3 value 830 is out of range."

Here is what formula looks like after I played with it a bit
=ArrayFormula
(INDEX('Pivot Table - Monthly'!$C$2:$O$2,
MATCH(MAX(INDEX('Pivot Table - Monthly'!$C$3:$O$999,0,
MATCH($C3,'Pivot Table - Monthly'!$B$3:$B$999,0))),
INDEX('Pivot Table - Monthly'!$C$3:$O$999,0,
MATCH($C3,'Pivot Table - Monthly'!$B$3:$B$999,0)),0
)))

 

[Aladin Akyurek]

Does C3 occur just once in 'PivotTable-Monthly'!$B$3:$B$999?

 

[dbarbella]

Yes
Basically I’m pasting this formula into a cell or row 3, and c3 holds the ID to lookup in the other sheet

When I copy and paste the same formula to row4, then it’ll look for $C4. And so I’m down the sheet

 

[Aladin Akyurek]

Is this what you are after?

=ARRAYFORMULA(INDEX('Pivot Table - Monthly'!$C$2:$O$2,MATCH(MAX(INDEX('Pivot Table - Monthly'!$C$3:$O$999,MATCH($C3,'Pivot Table - Monthly'!$B$3:$B$999,0),0)),INDEX('Pivot Table - Monthly'!$C$3:$O$999,MATCH($C3,'Pivot Table - Monthly'!$B$3:$B$999,0),0),0)))

 

[dbarbella]

Wow... that seems to be it.
Will further test, but after a few spot tests, it's working just right.

Genius!
Thank you Aladin