Row Number of Array Formula Result

N

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.

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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes

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)
 
Upvote 0
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
 
Upvote 0
AhoyNC said:
You can use MATCH to get the row # and INDEX for value in B
ABCDEF
1505953841882a0
2505953841882b200 7Row #
3505953841882c0 gValue in B
4505953841882d0
5505424741852
e0
6505424741852f0
7505424741852g500

<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
CellFormula
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
Click to expand...

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
 
Upvote 0

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.
 
Upvote 0
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
 
Upvote 0
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.

ABCDEFG
1505953841882a0Row #Value in B
2505953841882b2005059538418822b
3505953841882c05054247418527g
4505055541878
d200 505055541878 2
 b
5505055541878
e0
6505424741852f0
7505424741852g500

<tbody>
</tbody>
 
Upvote 0
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.
 
Upvote 0
Try:
Excel Workbook
ABCDEFG
1505953841882a0Row #Value in B
2505953841882b2005059538418822b
3505953841882c05054247418527g
4505055541878d2005050555418784d
5505055541878e0
6505424741852f0
7505424741852g500
Sheet
 
Upvote 0
AhoyNC said:
Try:


ABCDEFG
1505953841882a0Row #Value in B
2505953841882b2005059538418822b
3505953841882c05054247418527g
4505055541878d2005050555418784d
5505055541878e0
6505424741852f0
7505424741852g500

<tbody>
</tbody>

Spreadsheet Formulas
CellFormula
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
Click to expand...

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


Oeldere said:
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>
Click to expand...
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Index Match in VBA
Replies
1
Views
197
dermie_72
D
R
  • Question
Lowest value in range of cells excluding this row
2
Replies
10
Views
232
Peter_SSs
Peter_SSs
Z
  • Solved
Last Column Result
Replies
5
Views
219
Fluff
Fluff
A
  • Question
VBA if a row is all blank in a range of columns, highlight the blanks and the headers
2 3 4
Replies
31
Views
422
AnnAnn
A
R
  • Solved
Determine Date Range
Replies
3
Views
178
rwmill9716
R

Forum statistics

Threads
1,214,593
Messages
6,120,434
Members
448,961
Latest member
nzskater

Share this page

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