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:

x	m	3
y	m	3
x	n	3

<colgroup><col width="64" style="width:48pt" span="3"> </colgroup><tbody>
</tbody>

_{^{<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:

Row\Col	C​
2​	vad
3​	jad
4​	kad
5​	lad

Let's Stats house:

Row\Col	H​	I​	J​
2​	40​		x
3​	40​		y
4​	40​		z
5​	40​		w

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