Row Number of Array Formula Result

neb255

Board Regular
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.

AB
C
D
E
F
G
H
 505953841882

<colgroup><col width="92"></colgroup><tbody>
</tbody>
a
0
505953841882
 505953841882

<colgroup><col width="92"></colgroup><tbody>
</tbody>
b
200
 505424741852

<colgroup><col width="92"></colgroup><tbody>
</tbody>
 505953841882

<colgroup><col width="92"></colgroup><tbody>
</tbody>
c
0
 505953841882

<colgroup><col width="92"></colgroup><tbody>
</tbody>
d
0
 505424741852

<colgroup><col width="92"></colgroup><tbody>
</tbody>

<colgroup><col width="92"></colgroup><tbody>
</tbody>
e
0
 505424741852

<colgroup><col width="92"></colgroup><tbody>
</tbody>
f
0
 505424741852

<colgroup><col width="92"></colgroup><tbody>
</tbody>
g
500

<tbody>
</tbody>

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.

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Oeldere

Well-known Member

Book1
ABCDEFGHI
1#######a0#####5007g
2#######b200#####
3#######c0
4#######d0
5#######e0
6#######f0
7#######g500
Blad1
Cell Formulas
RangeFormula
G1=MAX(C1:C7)
H1=MATCH(G1,\$C\$1:\$C\$7,0)
I1=INDIRECT("B"&H1)

AhoyNC

Well-known Member
You can use MATCH to get the row # and INDEX for value in B
Excel Workbook
ABCDEF
1505953841882a0
2505953841882b2007Row #
3505953841882c0gValue in B
4505953841882d0
5505424741852e0
6505424741852f0
7505424741852g500
Sheet

neb255

Board Regular
You can use MATCH to get the row # and INDEX for value in B
 A B C D E F 1 505953841882 a 0 2 505953841882 b 200 7 Row # 3 505953841882 c 0 g Value in B 4 505953841882 d 0 5 505424741852 e 0 6 505424741852 f 0 7 505424741852 g 500

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:114px;"><col style="width:31px;"><col style="width:64px;"><col style="width:42px;"><col style="width:64px;"><col style="width:123px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
 Cell Formula E2 =MATCH(MAX(\$C\$1:\$C\$7),\$C\$1:\$C\$7,0) E3 =INDEX(\$B\$1:\$B\$7,MATCH(MAX(\$C\$1:\$C\$7),\$C\$1:\$C\$7,0))

<tbody>
</tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

I apologize if i was unclear in the description, column A is the unique value that i need to retrieve against. the formulas you list here will give me the max and row for the entire column, but not for each grouping of A.
ie.
505953841882 - Max is 200 in row 2 with a value of "b" from column B
505424741852 - Max is 500 in row 7 with a value of "g" from column B

Oeldere

Well-known Member

Book1
ABCDEFGHI
1505953841882a05059538418822002b
2505953841882b2005054247418525007g
3505953841882c0
4505953841882d0
5505424741852e0
6505424741852f0
7505424741852g500
Blad1
Cell Formulas
RangeFormula
H1=MATCH(G1,\$C\$1:\$C\$7,0)
H2=MATCH(G2,\$C\$1:\$C\$7,0)
I1=INDIRECT("B"&H1)
I2=INDIRECT("B"&H2)
G1{=MAX(IF(A:A=F1,C:C))}
G2{=MAX(IF(A:A=F2,C:C))}
Press CTRL+SHIFT+ENTER to enter array formulas.

AhoyNC

Well-known Member
Another option:
These are array formulas and must be entered with CTRL-SHIFT-ENTER.
Excel Workbook
ABCDEFG
1505953841882a0Row #Value in B
2505953841882b2005059538418822b
3505953841882c05054247418527g
4505953841882d0
5505424741852e0
6505424741852f0
7505424741852g500
Sheet

neb255

Board Regular
thanks, im still running into an issue when i apply this to my larger data-set. when the value in column C appears more than once the row number returned by the match is the first one in the column rather than for the specific subset of A.

 A B C D E F G 1 505953841882 a 0 Row # Value in B 2 505953841882 b 200 505953841882 2 b 3 505953841882 c 0 505424741852 7 g 4 505055541878 d 200 505055541878 2 b 5 505055541878 e 0 6 505424741852 f 0 7 505424741852 g 500

<tbody>
</tbody>

Oeldere

Well-known Member
See the new attempt (according to your request).

Book1
ABCDEFGH
1505953841882a050595384188205059538418822002
2505953841882b2005059538418822005054247418525007
3505953841882c050595384188205050555418782008
4505953841882d05059538418820
5505424741852e05054247418520
6505424741852f05054247418520
7505424741852g500505424741852500
8505055541878d200505055541878200
Blad1
Cell Formulas
RangeFormula
H1=MATCH(F1&G1,\$D\$1:\$D\$10,0)
H2=MATCH(F2&G2,\$D\$1:\$D\$10,0)
H3=MATCH(F3&G3,\$D\$1:\$D\$10,0)
D1=A1&C1
D2=A2&C2
D3=A3&C3
D4=A4&C4
D5=A5&C5
D6=A6&C6
D7=A7&C7
D8=A8&C8
G1{=MAX(IF(A:A=F1,C:C))}
G2{=MAX(IF(A:A=F2,C:C))}
G3{=MAX(IF(A:A=F3,C:C))}
Press CTRL+SHIFT+ENTER to enter array formulas.

AhoyNC

Well-known Member
Try:
Excel Workbook
ABCDEFG
1505953841882a0Row #Value in B
2505953841882b2005059538418822b
3505953841882c05054247418527g
4505055541878d2005050555418784d
5505055541878e0
6505424741852f0
7505424741852g500
Sheet

neb255

Board Regular
Try:

 A B C D E F G 1 505953841882 a 0 Row # Value in B 2 505953841882 b 200 505953841882 2 b 3 505953841882 c 0 505424741852 7 g 4 505055541878 d 200 505055541878 4 d 5 505055541878 e 0 6 505424741852 f 0 7 505424741852 g 500

<tbody>
</tbody>

Spreadsheet Formulas
 Cell Formula F2 {=MATCH(MAX(IF(\$A\$1:\$A\$7=\$E2,\$C\$1:\$C\$7)),IF(\$A\$1:\$A\$7=\$E2,\$C\$1:\$C\$7),0)} G2 {=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))}

<tbody>
</tbody>
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Thanks this works perfectly!!! Exactly what I was looking for.

See the new attempt (according to your request).

ABCDEFGH
1505953841882a050595384188205059538418822002
2505953841882b2005059538418822005054247418525007
3505953841882c050595384188205050555418782008
4505953841882d05059538418820
5505424741852e05054247418520
6505424741852f05054247418520
7505424741852g500505424741852500
8505055541878d200505055541878200

<tbody>
</tbody>
Blad1

Worksheet Formulas
CellFormula
H1=MATCH(F1&G1,\$D\$1:\$D\$10,0)
H2=MATCH(F2&G2,\$D\$1:\$D\$10,0)
H3=MATCH(F3&G3,\$D\$1:\$D\$10,0)
D1=A1&C1
D2=A2&C2
D3=A3&C3
D4=A4&C4
D5=A5&C5
D6=A6&C6
D7=A7&C7
D8=A8&C8

<tbody>
</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
G1{=MAX(IF(A:A=F1,C:C))}
G2{=MAX(IF(A:A=F2,C:C))}
G3{=MAX(IF(A:A=F3,C:C))}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>
Thanks, This works also, but since my full data-set is already over 100 columns and 100,000 rows I'm loathe to add anything more than I absolutely have to.

Replies
8
Views
153
Replies
36
Views
514
Replies
6
Views
130
Replies
0
Views
160
Replies
5
Views
196

Threads
1,191,121
Messages
5,984,765
Members
439,909
Latest member
daigoku

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

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