Smallest Number with Condition

[pto160]

I am trying to get the the row number of the smallest number in column B where column a="D". The column B numbers can change, so the formula will have to identify the smallest number automatically. The formula below is returning 4, which is incorrect.
<title>Excel Jeanie HTML</title>Sheet1

<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Arial,Arial; font-size: 10pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 64px;"> <col style="width: 64px;"> <col style="width: 64px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td> </td> <td>A</td> <td>B</td> <td>C</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">1</td> <td>A</td> <td style="text-align: right;">1</td> <td style="text-align: left;">Result</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">2</td> <td>B</td> <td style="text-align: right;">2</td> <td style="text-align: left;">7</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">3</td> <td>C</td> <td style="text-align: right;">3</td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">4</td> <td>D</td> <td style="text-align: right;">4</td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">5</td> <td>D</td> <td style="text-align: right;">3</td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">6</td> <td>D</td> <td style="text-align: right;">2</td> <td> </td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7</td> <td>D</td> <td style="text-align: right;">1</td> <td> </td></tr></tbody></table>

Excel tables to the web >> Excel Jeanie HTML 4

=INDEX(B1:B7,SMALL(IF((A1:A7="D")*(SMALL(B1:B7,1)),
ROW(B1:B7)),1))

 

[jasonb75]

<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: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></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></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">A</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">99</TD><TD> </TD><TD>Result</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">B</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">98</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD style="TEXT-ALIGN: right">6</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">C</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">100</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">D</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">99</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">D</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">101</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">D</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">99</TD><TD style="TEXT-ALIGN: right">1</TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">D</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">102</TD><TD style="TEXT-ALIGN: right">FALSE</TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>C2</TD><TD>=IF(AND(B2<B1,A2="D",A1="D"),ABS(100-B2))</TD></TR><TR><TD>D2</TD><TD>=MATCH(MIN(C1:C7),C1:C7,0)</TD></TR><TR><TD>C3</TD><TD>=IF(AND(B3<B2,A3="D",A2="D"),ABS(100-B3))</TD></TR><TR><TD>C4</TD><TD>=IF(AND(B4<B3,A4="D",A3="D"),ABS(100-B4))</TD></TR><TR><TD>C5</TD><TD>=IF(AND(B5<B4,A5="D",A4="D"),ABS(100-B5))</TD></TR><TR><TD>C6</TD><TD>=IF(AND(B6<B5,A6="D",A5="D"),ABS(100-B6))</TD></TR><TR><TD>C7</TD><TD>=IF(AND(B7<B6,A7="D",A6="D"),ABS(100-B7))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
Best guess based on the criteria given, as there was a requirement for the result row to have a value in B greater than the previous row, there is no need for a helper formula in C1, although this cell must still be referenced in the match formula.

 

[pto160]

I am trying to find lowest level of the hierarchical account for a particular group of accounts. The condition is the next row is "level" and the row before is a number.
Sheet1

<match(a18,a1:a15,0),>
<table style="background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt; font-family: Arial,Arial; font-size: 10pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup> <col style="width: 30px; font-weight: bold;"> <col style="width: 49px;"> <col style="width: 40px;"> <col style="width: 93px;"></colgroup> <tbody> <tr style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt; font-weight: bold;"> <td>
</td> <td>A</td> <td>B</td> <td>C</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">1</td> <td>
</td> <td style="text-align: left;">Detail</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">2</td> <td style="text-align: left;">LEVEL</td> <td style="text-align: left;">1</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">3</td> <td style="text-align: left;">LEVEL</td> <td style="text-align: left;">2</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">4</td> <td style="text-align: left;">LEVEL</td> <td style="text-align: left;">3</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">5</td> <td style="text-align: left;">10</td> <td style="text-align: left;">7</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">6</td> <td style="text-align: left;">20</td> <td style="text-align: left;">7</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">7</td> <td style="text-align: left;">30</td> <td style="text-align: left;">7</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">8</td> <td style="text-align: left;">LEVEL</td> <td style="text-align: left;">3</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">9</td> <td style="text-align: left;">LEVEL</td> <td style="text-align: left;">4</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">10</td> <td style="text-align: left;">LEVEL</td> <td style="text-align: left;">5</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">11</td> <td style="text-align: left;">40</td> <td style="text-align: left;">7</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">12</td> <td style="text-align: left;">50</td> <td style="text-align: left;">7</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">13</td> <td style="text-align: left;">LEVEL</td> <td style="text-align: left;">6</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">14</td> <td style="text-align: left;">60</td> <td style="text-align: left;">7</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">15</td> <td style="text-align: left;">70</td> <td style="text-align: left;">7</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">16</td> <td>
</td> <td>
</td> <td>
</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">17</td> <td style="text-align: left;">Lookup</td> <td style="text-align: left;">Row</td> <td style="text-align: left;">Result Desired</td></tr> <tr style="height: 17px;"> <td style="text-align: center; background-color: rgb(202, 202, 202); font-size: 8pt;">18</td> <td style="text-align: left;">70</td> <td style="text-align: left;">13</td> <td style="text-align: left;">8</td></tr></tbody></table>

Excel tables to the web >> Excel Jeanie HTML 4
</match(a18,a1:a15,0),><match(a18,a1:a15,0),><match(a18,a1:a15,0),>
</match(a18,a1:a15,0),>The code tags do not seem to be working properly. I will post the formula in the next post.

</match(a18,a1:a15,0),>

 

[pto160]

Here is the formula:
<match(a18,a1:a15,0),><match(a18,a1:a15,0),></match(a18,a1:a15,0),></match(a18,a1:a15,0),>

=LARGE(IF(ROW(A1:A15)-ROW(A1)+1"<"MATCH(A18,A1:A15,0),
IF(A1:A15="LEVEL",ROW(A1:A15)-ROW(A1)+1)),1)

The "<" in front of the match should be <. That was the only way to post it using the code tags.

 

[jasonb75]

Could you explain how you get a desired result of 8? From the details you gave in post #9 13 would appear to be the correct result.

Formula containing < aren't board friendly, adding a space before and after the < can cure the problem, as can tagging the formula as PHP

 

[pto160]

Thanks for the tip about < . 13 is the result based on the condition being "Level". 8 is the result based on the following conditions:

Condition 1="Level"
Condition 2= Next row (9) in col A is "Level"
Condition 3= Previous Row (7) in Col A is a number.

I included the large if array formula because the large function runs fast and is not volatile like offset.

 

[jasonb75]

So far I have 3 possible solutions, (think I've got the criteria sorted correctly), based on the sample table, this seems to be the most effective.

=2+MATCH(1,(A3:A14="LEVEL")*(A4:A15="LEVEL")*ISNUMBER(A2:A13))

Entered as array formula with shift ctrl enter.

 

[pto160]

Thanks. I left out something. With those "Level" conditions, it is the closest row to the Lookup, which is 70.

 

[jasonb75]

Try

{=LARGE(ROW(A3:A14)*(ROW(A3:A14)<MATCH(A18,A1:A15,0))*(A3:A14="LEVEL")*(A4:A15="LEVEL")*ISNUMBER(A2:A13),1)}

<?XML:NAMESPACE PREFIX = MATCH(A18,A1 /><MATCH(A18,A1:A15,0))*(A3:A14="LEVEL")*(A4:A15="LEVEL")*ISNUMBER(A2:A13),1)[ p php]<>array formula as before.
<MATCH(A18,A1:A15,0))*(A3:A14="LEVEL")*(A4:A15="LEVEL")*ISNUMBER(A2:A13),1)}< p>
I've assumed based on your Large function earlier that it should be the closest match before the lookup row, if it should look before and after then I think a helper column would be needed.
</MATCH(A18,A1:A15,0))*(A3:A14="LEVEL")*(A4:A15="LEVEL")*ISNUMBER(A2:A13),1)}<>
</MATCH(A18,A1:A15,0))*(A3:A14="LEVEL")*(A4:A15="LEVEL")*ISNUMBER(A2:A13),1)[>

 

[pto160]

Thanks. I followed one of your suggestions and used a helper column in col b and then the large if to find the row called "Top Level"

=IF((ISNUMBER(A2)*(A3="Level")*(A4="Level")),"Top Level","Next Level")

Thanks for all your help.