Row Number of Array Formula Result

neb255

Board Regular
Joined
Sep 14, 2011
Messages
64
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,
Code:
{=MAX(IF(A:A=F2,C:C))}
however if i try
Code:
 {=ROW(MAX(IF(A:A=F2,C:C)))}
i get an error.

Thanks.
 
read this one instead of my post #8


Book1
ABCDEFGHI
1505953841882a050595384188205059538418822002b
2505953841882b2005059538418822005054247418525007g
3505953841882c050595384188205050555418782008d
4505953841882d05059538418820
5505424741852e05054247418520
6505424741852f05054247418520
7505424741852g500505424741852500
8505055541878d200505055541878200
Blad1
Cell Formulas
RangeFormula
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.
 
Upvote 0
Code:
{=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))}
 
Upvote 0
@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?
 
Upvote 0
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]
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top