Row Number of Array Formula Result

[neb255]

Hi,

I am working with the below table, I need to get 2 things, the row number of the max value in C, and the value of column B.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 92"]
<colgroup><col width="92"></colgroup><tbody>[TR]
[TD="width: 92"]505953841882
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]a
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD][/TD]
[TD]505953841882
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 92"]
<colgroup><col width="92"></colgroup><tbody>[TR]
[TD="width: 92"]505953841882[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]b
[/TD]
[TD]200
[/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 92"]
<colgroup><col width="92"></colgroup><tbody>[TR]
[TD="width: 92"]505424741852[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 92"]
<colgroup><col width="92"></colgroup><tbody>[TR]
[TD="width: 92"]505953841882[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]c
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 92"]
<colgroup><col width="92"></colgroup><tbody>[TR]
[TD="width: 92"]505953841882[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]d
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 92"]
<colgroup><col width="92"></colgroup><tbody>[TR]
[TD="width: 92"][TABLE="width: 92"]
<colgroup><col width="92"></colgroup><tbody>[TR]
[TD="width: 92"]505424741852[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]e
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 92"]
<colgroup><col width="92"></colgroup><tbody>[TR]
[TD="width: 92"]505424741852[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]f
[/TD]
[TD]0
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 92"]
<colgroup><col width="92"></colgroup><tbody>[TR]
[TD="width: 92"]505424741852[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]g
[/TD]
[TD]500
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I am using the below formula in G2 to return the max value of an array,

{=MAX(IF(A:A=F2,C:C))}

however if i try

 {=ROW(MAX(IF(A:A=F2,C:C)))}

i get an error.

Thanks.

 

[Oeldere]

read this one instead of my post #8

Book1
	A	B	C	D	E	F	G	H	I
1	505953841882	a	0	5059538418820		505953841882	200	2	b
2	505953841882	b	200	505953841882200		505424741852	500	7	g
3	505953841882	c	0	5059538418820		505055541878	200	8	d
4	505953841882	d	0	5059538418820
5	505424741852	e	0	5054247418520
6	505424741852	f	0	5054247418520
7	505424741852	g	500	505424741852500
8	505055541878	d	200	505055541878200
Blad1

Cell Formulas
Range	Formula
H1	=MATCH(F1&G1,$D$1:$D$10,0)
I1	=INDIRECT("B"&H1)
D1	=A1&C1
G1	{=MAX(IF(A:A=F1,C:C))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[AhoyNC]

You're welcome. Third time is the charm!

 

[Oeldere]

{=MAX(IF(A:A=F1,C:C))}

Since you use so many rows (100.000) and column (100) you better use a range for the formula above to speed up the excel-file.

{=MAX(IF(A1:A100.000=F1,C1:C100.000))}

 

[Aladin Akyurek]

@neb255

Your sample seems to suggest that there is just one non-zero value for each item. Can you confirm that this is always true?

 

[neb255]

hi, sorry I didnt get back sooner. no the full dataset can have an unlimited varied set of values for each item. My final code was an adapted version (to the parameters of my spreadsheet) of the above response from AhoyNC[TABLE="class: cms_table"]
<tbody>[TR]
[TD]F2[/TD]
[TD]{=MATCH(MAX(IF($A$1:$A$7=$E2,$C$1:$C$7)),IF($A$1:$A$7=$E2,$C$1:$C$7),0)}[/TD]
[/TR]
[TR]
[TD]G2[/TD]
[TD]{=INDEX($B$1:$B$7,MATCH(MAX(IF($A$1:$A$7=$E2,$C$1:$C$7)),IF($A$1:$A$7=$E2,$C$1:$C$7),0))}[/TD]
[/TR]
</tbody>[/TABLE]