Number of the row with the first occurance

A

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.
 
Sort by date Sort by votes
Ama-Chan said:
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.
Click to expand...
Do you want the row number or do you really want the name from column A?

A = John
B = Jack
C = Jones
 
Upvote 0
Ama-Chan said:
I want the row number.
Click to expand...
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
Ama-Chan said:
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.
Click to expand...

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
Ama-Chan said:
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.
Click to expand...
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.
Click to expand...
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
mikerickson said:
Here's another CSE formula that returns the row number.
=MATCH(E2, REPT($B$1:$B$700, ($C$1:$C$700<4)), 0)
Click to expand...

Thanks this works perfectly.

T. Valko said:
So, if D = 9 then you actually want the value that is less than or equal to 4.
Click to expand...

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

Similar threads

S
  • Solved
Formula in one column to calculate the sumproduct of cells between two blank cells
Replies
9
Views
270
secrestj
S
D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
889
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
695
mumps
mumps
E
  • Question Question
Adding a custom message.
Replies
1
Views
709
Micron
M
R
  • Solved
Failed in order to derive Row number or Value in cell of particular Row Using Vlookup and Index... Match
2
Replies
10
Views
684
RapchikM
R

Forum statistics

Threads
1,226,892
Messages
6,193,538
Members
453,806
Latest member
Gene Michaels

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