Index, Match, Max, If

[reberryjr]

I'm using this array formula to return the max date where the primary key matches in 2 different worksheets, and it works fine.
{=MAX(IF(Specs!C:C='Info'!C2,Stats!H:H))}

I'm trying to get the value in column J on the Stats sheet, for the same line. I've been looking at this for a few hours. I've googled and googled, searched through this forum and 2 others. While I've found some information, I've not been able to tweak the formula to get the appropriate value. I want the formula to be dynamic if possible, because the data in the Stats sheet is dynamic.

I've tried these formulas, but none of them work:
{=INDEX(Specs!J:J,MAX(MATCH(Specs!C:C='Info'!C:C,Specs!H:H)))}
{=INDEX(Specs!J:J,MATCH(MAX(Specs!H:H),Specs!H:H,0))}
{=INDEX(Specs!J:J,MAX(MATCH(IF(Specs!C:C='Info'!C2,Specs!H:H),Specs!C:C,0)))}

The 2nd formula returns a number, but it's not associated with the correct record.

The end state goal is to enter the formula into the sheet via a macro, but I have to understand how the formula works first. Any help is appreciated.

 

[Legacy 143009]

You've got really close in the second one. Here is your formula:

{=INDEX(Stats!A:A,MATCH(MAX(IF(Specs!C:C=Info!C2,Stats!H:H)),Stats!H:H,0))}

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=INDEX(Stats!J:J,MATCH(MAX(IF(Specs!C:C=Info!C2,Stats!H:H)),IF(Specs!C:C=Info!C2,Stats!H:H),0))

This gives you the J-value that corresponds to the first instance of the MAX value, not possible others. This means the following:

[TABLE="class: grid, width: 192"]
<colgroup><col width="64" style="width:48pt" span="3"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent"]x[/TD]
[TD="width: 64, bgcolor: transparent"]m[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]y[/TD]
[TD="bgcolor: transparent"]m[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]x[/TD]
[TD="bgcolor: transparent"]n[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]
_{^{<strike>
</strike>}}
The max value when B = m, correlates with x and y in A, not just with x.
​

 

[reberryjr]

Thank you both for the reply!!! Aladin's formula seems to work the best. Now I get to try and nest some other formula in there! Woo hoo!

 

[Legacy 143009]

Thank you both for the reply!!! Aladin's formula seems to work the best. Now I get to try and nest some other formula in there! Woo hoo!

Ah right my formula checks for column A. For J:

{=INDEX(Stats!J:J,MATCH(MAX(IF(Specs!C:C=Info!C2,Stats!H:H)),Stats!H:H,0))}

You don't have to use the colum referance for INDEX function in this scenario.

 

[Aladin Akyurek]

Thank you both for the reply!!! Aladin's formula seems to work the best. Now I get to try and nest some other formula in there! Woo hoo!

You are welcome.

 

[Aladin Akyurek]

Ah right my formula checks for column A. For J:

{=INDEX(Stats!J:J,MATCH(MAX(IF(Specs!C:C=Info!C2,Stats!H:H)),Stats!H:H,0))}

You don't have to use the colum referance for INDEX function in this scenario.

Not good enough. It will potentially return a wrong J value.

 

[Legacy 143009]

Not good enough. It will potentially return a wrong J value.

Why?

 

[Aladin Akyurek]

Why?

Let's Specs house:

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

C​

[/td][/tr][tr][td]

2​

[/td][td]vad[/td][/tr]
[tr][td]

3​

[/td][td]jad[/td][/tr]
[tr][td]

4​

[/td][td]kad[/td][/tr]
[tr][td]

5​

[/td][td]lad[/td][/tr]
[/table]

Let's Stats house:

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][/tr][tr][td]

2​

[/td][td]

40​

[/td][td][/td][td]x[/td][/tr]
[tr][td]

3​

[/td][td]

40​

[/td][td][/td][td]y[/td][/tr]
[tr][td]

4​

[/td][td]

40​

[/td][td][/td][td]z[/td][/tr]
[tr][td]

5​

[/td][td]

40​

[/td][td][/td][td]w[/td][/tr]
[/table]

Let C2 of Info = jad

While:

=INDEX(Stats!J:J,MATCH(MAX(IF(Specs!C:C=Info!C2,Stats!H:H)),IF(Specs!C:C=Info!C2,Stats!H:H),0))

returns correctly y as result, the suggestion

=INDEX(Stats!J:J,MATCH(MAX(IF(Specs!C:C=Info!C2,Stats!H:H)),Stats!H:H,0))

must return x as result incorrectly for it underspecifies the match range.

 

[Wad Mabbit]

HI,
I have this, and it works fine so far...
=MAX(INDEX(LLN_Data[Reading], MATCH(1, ($M194=LLN_Data[Name]) * ("N/A"<>LLN_Data[Complete Time]), 0)))

LLN_Data[Reading are numbers
LLN_Data[Name is text
LLN_Data[Complete Time is date/time but may be as a text value