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]

@ dbarbella

You are welcome. Thanks for the update.

 

[Peter_SSs]

.. Google Sheets changed it slightly ...

As this is an Excel Questions forum, not a general spreadsheet forum, helpers are expecting to provide solutions in Excel form. Whilst we do not prohibit questions like yours, we would ask that in future you make it clear right at the start that you are using Google Sheets so the helpers at least know what they are being asked to do & can correctly decide if they want to participate in the thread.

 

[dbarbella]

May I ask one additional question?
I needed to add (True, Exact due to a case sensitivity issue.

I was able to apply it to the 2nd and 3rd MATCH statements, however, I am unable to determine the correct syntax adjustment of the first match instance (what part of the formula moves to different nested parenthesis.


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

 

[Aladin Akyurek]

Not clear. Are you requiring that C3 must be found in column B range as is: Dog as Dog, not as dog or DOG, etc.?

 

[dbarbella]

Hi Aladin,
Yes, that's right.


Just learned that some of those ID's are the same but with different character cases (which is very surprising that would ever have been allowed, but is the existing case).

So I changed my less complex Index/Match formulas from (just for example):
=INDEX('Tab 1'!$B$1:$B$9999,MATCH(A1,'Tab 1'!$A$1:$A$9999,0))

to (added TRUE, EXACT and also relocated the ,0 as necessary
=INDEX('Tab 1'!$B$1:$B$9999,MATCH(TRUE,EXACT(A1,'Tab 1'!$A$1:$A$9999),0))

I tried to apply (True,Exact to the complex formula achieved (again, incredibly grateful) in this thread.
I believe I have the 2nd and 3rd instance applied correctly after MATCH(

But the first instance of MATCH(MAX I can't seem to figure out how to apply it correctly

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

 

[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,MATCH(TRUE,EXACT($C3,'Pivot Table - Monthly'!$B$3:$B$999),0),0)),INDEX('Pivot Table - Monthly'!$C$3:$O$999,MATCH(TRUE,EXACT($C3,'Pivot Table - Monthly'!$B$3:$B$999),0),0),0))

In Google Sheets you need wrap the formula into an ARRAYFORMULA call. (Hope MS creates an equivalent like #(formula).)

 

[dbarbella]

Beautiful...I'm very grateful, thank you again.

Luckily, google auto adds the array wrapper when doing the CTRL/SHift/Enter combo

 

[Aladin Akyurek]

Beautiful...I'm very grateful, thank you again.

Luckily, google auto adds the array wrapper when doing the CTRL/SHift/Enter combo

You are welcome.