Number of the row with the first occurance

Ama-Chan

New Member
Joined
Oct 6, 2011
Messages
9
Hi.

I need to know the number of the row where 2 different columns specify to specific criteria. Here's an example of my sheet. It is not from the real sheet, but it represents what kind of formula I need.

<table style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:47px;"><col style="width:38px;"><col style="width:34px;"><col style="width:64px;"><col style="width:38px;"><col style="width:34px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td style="font-weight:bold; text-decoration:underline; ">Name</td><td style="font-weight:bold; text-decoration:underline; ">Class</td><td style="font-weight:bold; text-decoration:underline; ">Year</td><td>
</td><td style="font-weight:bold; text-decoration:underline; ">Class</td><td style="font-weight:bold; text-decoration:underline; ">Row</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td>John</td><td>A</td><td style="text-align:right; ">1</td><td>
</td><td>A</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td>James</td><td>A</td><td style="text-align:right; ">5</td><td>
</td><td>B</td><td>
</td></tr><tr style="height:19px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td>Jack</td><td>B</td><td style="text-align:right; ">2</td><td>
</td><td>C</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td>Jones</td><td>C</td><td style="text-align:right; ">1</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td>Jimmy</td><td>A</td><td style="text-align:right; ">2</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:19px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">7</td><td>Mark</td><td>C</td><td style="text-align:right; ">6</td><td>
</td><td>
</td><td>
</td></tr></tbody></table>
In F2 I need to have the number of first the row where B2:B7 matches E2 and where C2:C7 is lower then 4. For F3 it's the same except that it has to match E3 and so on. The value has to be lower than 4 and a 0 can also be a value in this list.

Thanks in advance.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Hi.

I need to know the number of the row where 2 different columns specify to specific criteria. Here's an example of my sheet. It is not from the real sheet, but it represents what kind of formula I need.

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 47px"><COL style="WIDTH: 38px"><COL style="WIDTH: 34px"><COL style="WIDTH: 64px"><COL style="WIDTH: 38px"><COL style="WIDTH: 34px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD>


</TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; TEXT-DECORATION: underline">Name</TD><TD style="FONT-WEIGHT: bold; TEXT-DECORATION: underline">Class</TD><TD style="FONT-WEIGHT: bold; TEXT-DECORATION: underline">Year</TD><TD>


</TD><TD style="FONT-WEIGHT: bold; TEXT-DECORATION: underline">Class</TD><TD style="FONT-WEIGHT: bold; TEXT-DECORATION: underline">Row</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>John</TD><TD>A</TD><TD style="TEXT-ALIGN: right">1</TD><TD>


</TD><TD>A</TD><TD>


</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>James</TD><TD>A</TD><TD style="TEXT-ALIGN: right">5</TD><TD>


</TD><TD>B</TD><TD>


</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>Jack</TD><TD>B</TD><TD style="TEXT-ALIGN: right">2</TD><TD>


</TD><TD>C</TD><TD>


</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>Jones</TD><TD>C</TD><TD style="TEXT-ALIGN: right">1</TD><TD>


</TD><TD>


</TD><TD>


</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>Jimmy</TD><TD>A</TD><TD style="TEXT-ALIGN: right">2</TD><TD>


</TD><TD>


</TD><TD>


</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>Mark</TD><TD>C</TD><TD style="TEXT-ALIGN: right">6</TD><TD>


</TD><TD>


</TD><TD>


</TD></TR></TBODY></TABLE>
In F2 I need to have the number of first the row where B2:B7 matches E2 and where C2:C7 is lower then 4. For F3 it's the same except that it has to match E3 and so on. The value has to be lower than 4 and a 0 can also be a value in this list.

Thanks in advance.
Do you want the row number or do you really want the name from column A?

A = John
B = Jack
C = Jones
 
Upvote 0
I want the row number.
Try this...

Book1
ABCDEF
2JohnA1_A2
3JamesA5_B4
4JackB2_C5
5JonesC1___
6JimmyA2___
7MarkC6___
Sheet1

This array formula** entered in F2 and copied down:

=MIN(IF(B$2:B$7=E2,IF(C$2:C$7<4,ROW(C$2:C$7))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
 
Upvote 0
Hi.

I need to know the number of the row where 2 different columns specify to specific criteria. Here's an example of my sheet. It is not from the real sheet, but it represents what kind of formula I need.

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 47px"><COL style="WIDTH: 38px"><COL style="WIDTH: 34px"><COL style="WIDTH: 64px"><COL style="WIDTH: 38px"><COL style="WIDTH: 34px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD>


</TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-WEIGHT: bold; TEXT-DECORATION: underline">Name</TD><TD style="FONT-WEIGHT: bold; TEXT-DECORATION: underline">Class</TD><TD style="FONT-WEIGHT: bold; TEXT-DECORATION: underline">Year</TD><TD>


</TD><TD style="FONT-WEIGHT: bold; TEXT-DECORATION: underline">Class</TD><TD style="FONT-WEIGHT: bold; TEXT-DECORATION: underline">Row</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>John</TD><TD>A</TD><TD style="TEXT-ALIGN: right">1</TD><TD>


</TD><TD>A</TD><TD>


</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>James</TD><TD>A</TD><TD style="TEXT-ALIGN: right">5</TD><TD>


</TD><TD>B</TD><TD>


</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Jack</TD><TD>B</TD><TD style="TEXT-ALIGN: right">2</TD><TD>


</TD><TD>C</TD><TD>


</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>Jones</TD><TD>C</TD><TD style="TEXT-ALIGN: right">1</TD><TD>


</TD><TD>


</TD><TD>


</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>Jimmy</TD><TD>A</TD><TD style="TEXT-ALIGN: right">2</TD><TD>


</TD><TD>


</TD><TD>


</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>Mark</TD><TD>C</TD><TD style="TEXT-ALIGN: right">6</TD><TD>


</TD><TD>


</TD><TD>


</TD></TR></TBODY></TABLE>
In F2 I need to have the number of first the row where B2:B7 matches E2 and where C2:C7 is lower then 4. For F3 it's the same except that it has to match E3 and so on. The value has to be lower than 4 and a 0 can also be a value in this list.

Thanks in advance.

Native row number corresponding to the first matching value...

F2, control+shift+enter, not just enter, and copy down

=MIN(IF($B$2:$B$7=E2,IF($C$2:$C$7 < 4, ROW($B$2:$B$7))))

If you want to return the first corresponding name...

=INDEX($A$2:$A$7,MIN(IF($B$2:$B$7=E2,IF($C$2:$C$7 < 4, ROW($B$2:$B$7)))))

If you want some control, these formulas can be wrapped into a CountIf expression...

=IF(COUNTIF($B$2:$B$7,E2),MIN(IF($B$2:$B$7=E2,IF($C$2:$C$7 < 4, ROW($B$2:$B$7)))),"")

Use the same set up for the second formula.

_________________
Posted from Istanbul.
 
Upvote 0
In the text example it worked fine, but in the real sheet it produces some errors.
I have extended the text sheet for a moment and with the formula you gave me this is the result.

<table style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:63px;"><col style="width:38px;"><col style="width:34px;"><col style="width:64px;"><col style="width:38px;"><col style="width:34px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td> </td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td style="font-weight:bold; text-decoration:underline; ">Name</td><td style="font-weight:bold; text-decoration:underline; ">Class</td><td style="font-weight:bold; text-decoration:underline; ">Year</td><td> </td><td style="font-weight:bold; text-decoration:underline; ">Class</td><td style="font-weight:bold; text-decoration:underline; ">Row</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td>John</td><td>A</td><td style="text-align:right; ">1</td><td> </td><td>A</td><td style="text-align:right; ">2</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td>James</td><td>A</td><td style="text-align:right; ">5</td><td> </td><td>B</td><td style="text-align:right; ">4</td></tr><tr style="height:19px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td>Jack</td><td>B</td><td style="text-align:right; ">2</td><td> </td><td>C</td><td style="text-align:right; ">6</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td>Abraham</td><td>C</td><td style="text-align:right; ">4</td><td> </td><td>D</td><td style="text-align:right; ">0</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td>Jones</td><td>C</td><td style="text-align:right; ">1</td><td> </td><td> </td><td> </td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">7</td><td>Jimmy</td><td>A</td><td style="text-align:right; ">2</td><td> </td><td> </td><td> </td></tr><tr style="height:19px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">8</td><td>Mark</td><td>C</td><td style="text-align:right; ">6</td><td> </td><td> </td><td> </td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">9</td><td>Daniel</td><td>D</td><td style="text-align:right; ">4</td><td> </td><td> </td><td> </td></tr></tbody></table>
<table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family:Arial; font-size:9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color:#cacaca; font-size:10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>F2</td><td>{=MIN(IF(B$2:B$9=E2;IF(C$2:C$9<4;ROW(C$2:C$9))))}</td></tr><tr><td>F3</td><td>{=MIN(IF(B$2:B$9=E3;IF(C$2:C$9<4;ROW(C$2:C$9))))}</td></tr><tr><td>F4</td><td>{=MIN(IF(B$2:B$9=E4;IF(C$2:C$9<4;ROW(C$2:C$9))))}</td></tr><tr><td>F5</td><td>{=MIN(IF(B$2:B$9=E5;IF(C$2:C$9<4;ROW(C$2:C$9))))}</td></tr></tbody></table></td></tr><tr><td>Formula Array:
Produce enclosing
{ } by entering

</td></tr></tbody></table>
Now this is exactly what I want, but in the real sheet that I'm working on this is what happens.

<table style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:63px;"><col style="width:38px;"><col style="width:34px;"><col style="width:64px;"><col style="width:38px;"><col style="width:34px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td> </td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td style="font-weight:bold; text-decoration:underline; ">Name</td><td style="font-weight:bold; text-decoration:underline; ">Class</td><td style="font-weight:bold; text-decoration:underline; ">Year</td><td> </td><td style="font-weight:bold; text-decoration:underline; ">Class</td><td style="font-weight:bold; text-decoration:underline; ">Row</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td>John</td><td>A</td><td style="text-align:right; ">1</td><td> </td><td>A</td><td style="text-align:right; ">0</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td>James</td><td>A</td><td style="text-align:right; ">5</td><td> </td><td>B</td><td style="text-align:right; ">4</td></tr><tr style="height:19px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td>Jack</td><td>B</td><td style="text-align:right; ">2</td><td> </td><td>C</td><td style="text-align:right; ">5</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td>Abraham</td><td>C</td><td style="text-align:right; ">4</td><td> </td><td>D</td><td style="text-align:right; ">9</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td>Jones</td><td>C</td><td style="text-align:right; ">1</td><td> </td><td> </td><td> </td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">7</td><td>Jimmy</td><td>A</td><td style="text-align:right; ">2</td><td> </td><td> </td><td> </td></tr><tr style="height:19px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">8</td><td>Mark</td><td>C</td><td style="text-align:right; ">6</td><td> </td><td> </td><td> </td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">9</td><td>Daniel</td><td>D</td><td style="text-align:right; ">4</td><td> </td><td> </td><td> </td></tr></tbody></table>
<table style="font-family: Arial; font-size: 10pt; border-style: groove; border-color: rgb(0, 255, 0); background-color: rgb(255, 252, 249); color: rgb(0, 0, 0); width: 69px; height: 13px;"><tbody><tr><td>
</td></tr><tr><td>
</td></tr><tr><td>
</td></tr></tbody></table>

This is the formula I used in the real sheet and I don't think I did something wrong with it.

{=MIN(IF($G$2:$G$1000=AD46;IF($U$2:$U$1000<4;ROW($G$2:$G$1000))))}

Yes, I pressed CTRL + SHIFT + ENTER to enter it and I didn't type the { and the } in the code.
 
Upvote 0
Here's another CSE formula that returns the row number.
=MATCH(E2, REPT($B$1:$B$700, ($C$1:$C$700<4)), 0)
 
Upvote 0
In the text example it worked fine, but in the real sheet it produces some errors.
I have extended the text sheet for a moment and with the formula you gave me this is the result.

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 63px"><COL style="WIDTH: 38px"><COL style="WIDTH: 34px"><COL style="WIDTH: 64px"><COL style="WIDTH: 38px"><COL style="WIDTH: 34px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; TEXT-DECORATION: underline">Name</TD><TD style="FONT-WEIGHT: bold; TEXT-DECORATION: underline">Class</TD><TD style="FONT-WEIGHT: bold; TEXT-DECORATION: underline">Year</TD><TD></TD><TD style="FONT-WEIGHT: bold; TEXT-DECORATION: underline">Class</TD><TD style="FONT-WEIGHT: bold; TEXT-DECORATION: underline">Row</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>John</TD><TD>A</TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD>A</TD><TD style="TEXT-ALIGN: right">2</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>James</TD><TD>A</TD><TD style="TEXT-ALIGN: right">5</TD><TD></TD><TD>B</TD><TD style="TEXT-ALIGN: right">4</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>Jack</TD><TD>B</TD><TD style="TEXT-ALIGN: right">2</TD><TD></TD><TD>C</TD><TD style="TEXT-ALIGN: right">6</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>Abraham</TD><TD>C</TD><TD style="TEXT-ALIGN: right">4</TD><TD></TD><TD>D</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>Jones</TD><TD>C</TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>Jimmy</TD><TD>A</TD><TD style="TEXT-ALIGN: right">2</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD>Mark</TD><TD>C</TD><TD style="TEXT-ALIGN: right">6</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD>Daniel</TD><TD>D</TD><TD style="TEXT-ALIGN: right">4</TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>F2</TD><TD>{=MIN(IF(B$2:B$9=E2;IF(C$2:C$9<4;ROW(C$2:C$9))))}</TD></TR><TR><TD>F3</TD><TD>{=MIN(IF(B$2:B$9=E3;IF(C$2:C$9<4;ROW(C$2:C$9))))}</TD></TR><TR><TD>F4</TD><TD>{=MIN(IF(B$2:B$9=E4;IF(C$2:C$9<4;ROW(C$2:C$9))))}</TD></TR><TR><TD>F5</TD><TD>{=MIN(IF(B$2:B$9=E5;IF(C$2:C$9<4;ROW(C$2:C$9))))}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering



</TD></TR></TBODY></TABLE>
Now this is exactly what I want, but in the real sheet that I'm working on this is what happens.

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 63px"><COL style="WIDTH: 38px"><COL style="WIDTH: 34px"><COL style="WIDTH: 64px"><COL style="WIDTH: 38px"><COL style="WIDTH: 34px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="FONT-WEIGHT: bold; TEXT-DECORATION: underline">Name</TD><TD style="FONT-WEIGHT: bold; TEXT-DECORATION: underline">Class</TD><TD style="FONT-WEIGHT: bold; TEXT-DECORATION: underline">Year</TD><TD></TD><TD style="FONT-WEIGHT: bold; TEXT-DECORATION: underline">Class</TD><TD style="FONT-WEIGHT: bold; TEXT-DECORATION: underline">Row</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD>John</TD><TD>A</TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD>A</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD>James</TD><TD>A</TD><TD style="TEXT-ALIGN: right">5</TD><TD></TD><TD>B</TD><TD style="TEXT-ALIGN: right">4</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD>Jack</TD><TD>B</TD><TD style="TEXT-ALIGN: right">2</TD><TD></TD><TD>C</TD><TD style="TEXT-ALIGN: right">5</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD>Abraham</TD><TD>C</TD><TD style="TEXT-ALIGN: right">4</TD><TD></TD><TD>D</TD><TD style="TEXT-ALIGN: right">9</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD>Jones</TD><TD>C</TD><TD style="TEXT-ALIGN: right">1</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>Jimmy</TD><TD>A</TD><TD style="TEXT-ALIGN: right">2</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD>Mark</TD><TD>C</TD><TD style="TEXT-ALIGN: right">6</TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD>Daniel</TD><TD>D</TD><TD style="TEXT-ALIGN: right">4</TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: rgb(0,255,0); BORDER-BOTTOM-COLOR: rgb(0,255,0); WIDTH: 69px; COLOR: rgb(0,0,0); BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: rgb(0,255,0); FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; HEIGHT: 13px; BACKGROUND-COLOR: rgb(255,252,249); BORDER-RIGHT-COLOR: rgb(0,255,0); BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>


</TD></TR><TR><TD>


</TD></TR><TR><TD>


</TD></TR></TBODY></TABLE>

This is the formula I used in the real sheet and I don't think I did something wrong with it.

{=MIN(IF($G$2:$G$1000=AD46;IF($U$2:$U$1000<4;ROW($G$2:$G$1000))))}

Yes, I pressed CTRL + SHIFT + ENTER to enter it and I didn't type the { and the } in the code.
Why is D = 9?

You said:

In F2 I need to have the number of first the row where B2:B7 matches E2 and where C2:C7 is lower then 4.
So, if D = 9 then you actually want the value that is less than or equal to 4.

In that case simply change <4 to <=4.

Still array entered**:

=MIN(IF($G$2:$G$1000=AD46;IF($U$2:$U$1000<=4;ROW($G$2:$G$1000))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
 
Upvote 0
Here's another CSE formula that returns the row number.
=MATCH(E2, REPT($B$1:$B$700, ($C$1:$C$700<4)), 0)

Thanks this works perfectly.

So, if D = 9 then you actually want the value that is less than or equal to 4.

No that is not what I wanted that is what Excel made of it. The first sheet of what I posted in post #6 is what I actually wanted and the second is what Excel made of it.
 
Upvote 0
No that is not what I wanted that is what Excel made of it. The first sheet of what I posted in post #6 is what I actually wanted and the second is what Excel made of it.
Here's a small sample file that demonstrates this.

zzzAma-Chan.xls 16kb

http://cjoint.com/?AJtpGgGeiLG

As you'll see the formula does return the correct results. If in your file you do not get the correct results then there must be some problem with your data.

The article at this link describes some common data problems that can cause problems.

http://contextures.com/xlFunctions02.html#Trouble
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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