How to find

B

BJIII

New Member
Joined
Nov 8, 2010
Messages
26
Hello,

I am looking to find the 1st percentage less than 1% in a range of cells and then have the formula report a quantity that is in the same row but two columns over.


<TABLE style="WIDTH: 340pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=450><COLGROUP><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3291" span=5 width=90><TBODY><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 68pt; HEIGHT: 16.5pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl82 height=22 width=90>Confidence</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 68pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl82 width=90>Ppk Goal</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 68pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl82 width=90>Kn</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 68pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl82 width=90>Required Ppk</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 68pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl82 width=90>% of Change</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 height=20>92%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85>1.67</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl83>9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl86>2.57</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>10.05%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 height=20>92%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85>1.67</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl83>12</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl86>2.38</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>5.95%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 height=20>92%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85>1.67</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl83>15</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl86>2.27</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>4.03%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 height=20>92%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85>1.67</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl83>18</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl86>2.20</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>2.95%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 height=20>92%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85>1.67</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl83>21</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl86>2.15</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>2.27%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 height=20>92%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85>1.67</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl83>24</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl86>2.11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>1.82%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 height=20>92%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85>1.67</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl83>27</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl86>2.07</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>1.50%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 height=20>92%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85>1.67</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl83>30</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl86>2.05</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>1.26%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 height=20>92%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85>1.67</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl83>33</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl86>2.03</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>1.08%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 height=20>92%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85>1.67</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl83>36</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl86>2.01</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>0.94%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 height=20>92%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85>1.67</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl83>39</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl86>1.99</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>0.82%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 height=20>92%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85>1.67</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl83>42</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl86>1.98</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>0.73%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 height=20>92%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85>1.67</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl83>45</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl86>1.96</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>0.65%</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 height=21>92%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85>1.67</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl83>48</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl86>1.95</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>0.59%</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 height=21>92%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85>1.67</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl83>51</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl86>1.94</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>0.54%</TD></TR></TBODY></TABLE>

So I am looking to find the .94% in the % of change column and have the formula report 36 from the Kn column.

Any help would be appreciated!

Thanks,
BJW
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
hello,
Assuming that the "% of change" column is in descending order, here is one way. Use Ctrl+shift+Enter because this is an array formula. Range E2:E16 is the "% of change" column.

{=OFFSET(INDEX(E2:E16,MATCH(0,IF(E2:E16<0.01,),0)),,-2)}
 
Upvote 0
Trouttrap2 said:
yes it can. I shouldn't have said "in ascending order".
Click to expand...

Hi Trouttrap2

Appreciate if u can help me with a formula in cell C17 to C21 that can extract the items from the Raw Data if I enter the Supplier in cell E14 ... and the result listed in cell C17 to C21.


<table style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" border="0" cellpadding="0" cellspacing="0"> <colgroup> <col style="FONT-WEIGHT: bold; WIDTH: 30px"> <col style="WIDTH: 82px"> <col style="WIDTH: 31px"> <col style="WIDTH: 31px"> <col style="WIDTH: 31px"></colgroup> <tbody> <tr style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"> <td> </td> <td>C</td> <td>D</td> <td>E</td> <td>F</td></tr> <tr style="HEIGHT: 17px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</td> <td style="BORDER-TOP-WIDTH: 1px; FONT-WEIGHT: bold; BORDER-LEFT-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #c0c0c0; COLOR: #0000ff; BORDER-TOP-COLOR: #c0c0c0; BORDER-BOTTOM: #c0c0c0 1px solid; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #c0c0c0">Raw Data</td> <td style="BORDER-TOP-WIDTH: 1px; BORDER-LEFT-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #c0c0c0; BORDER-TOP-COLOR: #c0c0c0; BORDER-BOTTOM: #c0c0c0 1px solid; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #c0c0c0"> </td> <td style="BORDER-TOP-WIDTH: 1px; BORDER-LEFT-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #c0c0c0; BORDER-TOP-COLOR: #c0c0c0; BORDER-BOTTOM: #c0c0c0 1px solid; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #c0c0c0"> </td> <td style="BORDER-TOP-WIDTH: 1px; BORDER-LEFT-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #c0c0c0; BORDER-TOP-COLOR: #c0c0c0; BORDER-BOTTOM: #c0c0c0 1px solid; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #c0c0c0"> </td></tr> <tr style="HEIGHT: 17px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</td> <td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #c0c0c0 1px solid; FONT-SIZE: 8pt; BORDER-LEFT: #c0c0c0 1px solid; COLOR: #ff0000; BORDER-TOP-COLOR: #c0c0c0; BORDER-BOTTOM: #c0c0c0 1px solid; TEXT-ALIGN: center">Items</td> <td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #c0c0c0 1px solid; BORDER-LEFT-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #c0c0c0; COLOR: #ff0000; BORDER-TOP-COLOR: #c0c0c0; BORDER-BOTTOM: #c0c0c0 1px solid; TEXT-ALIGN: center" colspan="3">Suppliers</td></tr> <tr style="HEIGHT: 17px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</td> <td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #c0c0c0 1px solid; FONT-SIZE: 8pt; BORDER-LEFT: #c0c0c0 1px solid; BORDER-TOP-COLOR: #c0c0c0; BORDER-BOTTOM: #c0c0c0 1px solid">Apple</td> <td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #c0c0c0 1px solid; BORDER-LEFT-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #c0c0c0; BORDER-TOP-COLOR: #c0c0c0; BORDER-BOTTOM: #c0c0c0 1px solid; TEXT-ALIGN: center">A</td> <td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #c0c0c0 1px solid; BORDER-LEFT-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #c0c0c0; BORDER-TOP-COLOR: #c0c0c0; BORDER-BOTTOM: #c0c0c0 1px solid; TEXT-ALIGN: center">B</td> <td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #c0c0c0 1px solid; BORDER-LEFT-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #c0c0c0; BORDER-TOP-COLOR: #c0c0c0; BORDER-BOTTOM: #c0c0c0 1px solid; TEXT-ALIGN: center">D</td></tr> <tr style="HEIGHT: 17px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</td> <td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #c0c0c0 1px solid; FONT-SIZE: 8pt; BORDER-LEFT: #c0c0c0 1px solid; BORDER-TOP-COLOR: #c0c0c0; BORDER-BOTTOM: #c0c0c0 1px solid">Orange</td> <td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #c0c0c0 1px solid; BORDER-LEFT-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #c0c0c0; BORDER-TOP-COLOR: #c0c0c0; BORDER-BOTTOM: #c0c0c0 1px solid; TEXT-ALIGN: center">A</td> <td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #c0c0c0 1px solid; BORDER-LEFT-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #c0c0c0; BORDER-TOP-COLOR: #c0c0c0; BORDER-BOTTOM: #c0c0c0 1px solid; TEXT-ALIGN: center">B</td> <td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #c0c0c0 1px solid; BORDER-LEFT-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #c0c0c0; BORDER-TOP-COLOR: #c0c0c0; BORDER-BOTTOM: #c0c0c0 1px solid; TEXT-ALIGN: center">C</td></tr> <tr style="HEIGHT: 17px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</td> <td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #c0c0c0 1px solid; FONT-SIZE: 8pt; BORDER-LEFT: #c0c0c0 1px solid; BORDER-TOP-COLOR: #c0c0c0; BORDER-BOTTOM: #c0c0c0 1px solid">Grape</td> <td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #c0c0c0 1px solid; BORDER-LEFT-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #c0c0c0; BORDER-TOP-COLOR: #c0c0c0; BORDER-BOTTOM: #c0c0c0 1px solid; TEXT-ALIGN: center">A</td> <td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #c0c0c0 1px solid; BORDER-LEFT-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #c0c0c0; BORDER-TOP-COLOR: #c0c0c0; BORDER-BOTTOM: #c0c0c0 1px solid; TEXT-ALIGN: center">C</td> <td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #c0c0c0 1px solid; BORDER-LEFT-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #c0c0c0; BORDER-TOP-COLOR: #c0c0c0; BORDER-BOTTOM: #c0c0c0 1px solid; TEXT-ALIGN: center">D</td></tr> <tr style="HEIGHT: 17px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</td> <td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #c0c0c0 1px solid; FONT-SIZE: 8pt; BORDER-LEFT: #c0c0c0 1px solid; BORDER-TOP-COLOR: #c0c0c0; BORDER-BOTTOM: #c0c0c0 1px solid">Melon</td> <td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #c0c0c0 1px solid; BORDER-LEFT-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #c0c0c0; BORDER-TOP-COLOR: #c0c0c0; BORDER-BOTTOM: #c0c0c0 1px solid; TEXT-ALIGN: center">B</td> <td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #c0c0c0 1px solid; BORDER-LEFT-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #c0c0c0; BORDER-TOP-COLOR: #c0c0c0; BORDER-BOTTOM: #c0c0c0 1px solid; TEXT-ALIGN: center">C</td> <td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #c0c0c0 1px solid; BORDER-LEFT-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #c0c0c0; BORDER-TOP-COLOR: #c0c0c0; BORDER-BOTTOM: #c0c0c0 1px solid"> </td></tr> <tr style="HEIGHT: 17px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</td> <td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #c0c0c0 1px solid; FONT-SIZE: 8pt; BORDER-LEFT: #c0c0c0 1px solid; BORDER-TOP-COLOR: #c0c0c0; BORDER-BOTTOM: #c0c0c0 1px solid">Banana</td> <td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #c0c0c0 1px solid; BORDER-LEFT-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #c0c0c0; BORDER-TOP-COLOR: #c0c0c0; BORDER-BOTTOM: #c0c0c0 1px solid; TEXT-ALIGN: center">B</td> <td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #c0c0c0 1px solid; BORDER-LEFT-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #c0c0c0; BORDER-TOP-COLOR: #c0c0c0; BORDER-BOTTOM: #c0c0c0 1px solid; TEXT-ALIGN: center">A</td> <td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #c0c0c0 1px solid; BORDER-LEFT-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #c0c0c0; BORDER-TOP-COLOR: #c0c0c0; BORDER-BOTTOM: #c0c0c0 1px solid"> </td></tr> <tr style="HEIGHT: 17px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td></tr> <tr style="HEIGHT: 17px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">13</td> <td style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; COLOR: #ff0000">Search Result</td> <td style="FONT-SIZE: 8pt"> </td> <td style="BORDER-TOP-WIDTH: 1px; BORDER-LEFT-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000"> </td> <td style="FONT-SIZE: 8pt"> </td></tr> <tr style="HEIGHT: 17px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">14</td> <td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM-WIDTH: 1px; BORDER-BOTTOM-COLOR: #000000; BORDER-TOP-COLOR: #000000; TEXT-ALIGN: right" colspan="2">Enter Supplier =</td> <td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">C</td> <td style="FONT-SIZE: 8pt"> </td></tr> <tr style="HEIGHT: 17px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">15</td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td></tr> <tr style="HEIGHT: 17px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">16</td> <td style="BORDER-TOP-WIDTH: 1px; FONT-WEIGHT: bold; BORDER-LEFT-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #969696; COLOR: #ff0000; BORDER-TOP-COLOR: #969696; BORDER-BOTTOM: #969696 1px solid; BORDER-RIGHT-WIDTH: 1px; BORDER-RIGHT-COLOR: #969696">Items</td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td></tr> <tr style="HEIGHT: 17px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">17</td> <td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #969696 1px solid; FONT-SIZE: 8pt; BORDER-LEFT: #969696 1px solid; BORDER-TOP-COLOR: #969696; BORDER-BOTTOM: #969696 1px solid">Orange</td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td></tr> <tr style="HEIGHT: 17px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">18</td> <td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #969696 1px solid; FONT-SIZE: 8pt; BORDER-LEFT: #969696 1px solid; BORDER-TOP-COLOR: #969696; BORDER-BOTTOM: #969696 1px solid">Grape</td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td></tr> <tr style="HEIGHT: 17px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">19</td> <td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #969696 1px solid; FONT-SIZE: 8pt; BORDER-LEFT: #969696 1px solid; BORDER-TOP-COLOR: #969696; BORDER-BOTTOM: #969696 1px solid">Melon</td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td></tr> <tr style="HEIGHT: 17px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">20</td> <td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #969696 1px solid; FONT-SIZE: 8pt; BORDER-LEFT: #969696 1px solid; BORDER-TOP-COLOR: #969696; BORDER-BOTTOM: #969696 1px solid"> </td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td></tr> <tr style="HEIGHT: 17px"> <td style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">21</td> <td style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #969696 1px solid; FONT-SIZE: 8pt; BORDER-LEFT: #969696 1px solid; BORDER-TOP-COLOR: #969696; BORDER-BOTTOM: #969696 1px solid"> </td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td> <td style="FONT-SIZE: 8pt"> </td></tr></tbody></table>
cheers
 
Upvote 0
BJIII said:
Hello,

I am looking to find the 1st percentage less than 1% in a range of cells and then have the formula report a quantity that is in the same row but two columns over.


<TABLE style="WIDTH: 340pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=450><COLGROUP><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3291" span=5 width=90><TBODY><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 68pt; HEIGHT: 16.5pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl82 height=22 width=90>Confidence</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 68pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl82 width=90>Ppk Goal</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 68pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl82 width=90>Kn</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 68pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl82 width=90>Required Ppk</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 68pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl82 width=90>% of Change</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 height=20>92%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85>1.67</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl83>9</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl86>2.57</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>10.05%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 height=20>92%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85>1.67</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl83>12</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl86>2.38</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>5.95%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 height=20>92%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85>1.67</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl83>15</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl86>2.27</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>4.03%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 height=20>92%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85>1.67</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl83>18</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl86>2.20</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>2.95%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 height=20>92%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85>1.67</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl83>21</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl86>2.15</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>2.27%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 height=20>92%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85>1.67</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl83>24</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl86>2.11</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>1.82%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 height=20>92%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85>1.67</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl83>27</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl86>2.07</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>1.50%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 height=20>92%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85>1.67</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl83>30</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl86>2.05</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>1.26%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 height=20>92%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85>1.67</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl83>33</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl86>2.03</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>1.08%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 height=20>92%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85>1.67</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl83>36</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl86>2.01</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>0.94%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 height=20>92%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85>1.67</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl83>39</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl86>1.99</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>0.82%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 height=20>92%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85>1.67</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl83>42</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl86>1.98</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>0.73%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 height=20>92%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85>1.67</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl83>45</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl86>1.96</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>0.65%</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 height=21>92%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85>1.67</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl83>48</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl86>1.95</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>0.59%</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl84 height=21>92%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl85>1.67</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl83>51</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl86>1.94</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl87>0.54%</TD></TR></TBODY></TABLE>

So I am looking to find the .94% in the % of change column and have the formula report 36 from the Kn column.

Any help would be appreciated!

Thanks,
BJW
Click to expand...
Control+shift+enter, not just enter:

=INDEX($C$2:$C$16, MATCH(TRUE, $E$2:$E$16 < 0.01, 0))
 
Upvote 0
jamshoot said:
Hi Trouttrap2

Appreciate if u can help me with a formula in cell C17 to C21 that can extract the items from the Raw Data if I enter the Supplier in cell E14 ... and the result listed in cell C17 to C21.


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=0 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 82px"><COL style="WIDTH: 31px"><COL style="WIDTH: 31px"><COL style="WIDTH: 31px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="BORDER-BOTTOM: #c0c0c0 1px solid; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #c0c0c0; BORDER-TOP-WIDTH: 1px; COLOR: #0000ff; BORDER-RIGHT-COLOR: #c0c0c0; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #c0c0c0; BORDER-LEFT-WIDTH: 1px; FONT-WEIGHT: bold">Raw Data</TD><TD style="BORDER-BOTTOM: #c0c0c0 1px solid; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #c0c0c0; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT-COLOR: #c0c0c0; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #c0c0c0; BORDER-LEFT-WIDTH: 1px"></TD><TD style="BORDER-BOTTOM: #c0c0c0 1px solid; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #c0c0c0; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT-COLOR: #c0c0c0; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #c0c0c0; BORDER-LEFT-WIDTH: 1px"></TD><TD style="BORDER-BOTTOM: #c0c0c0 1px solid; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #c0c0c0; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT-COLOR: #c0c0c0; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #c0c0c0; BORDER-LEFT-WIDTH: 1px"></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="BORDER-BOTTOM: #c0c0c0 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #c0c0c0 1px solid; BORDER-TOP-COLOR: #c0c0c0; BORDER-TOP-WIDTH: 1px; COLOR: #ff0000; FONT-SIZE: 8pt; BORDER-RIGHT: #c0c0c0 1px solid">Items</TD><TD style="BORDER-BOTTOM: #c0c0c0 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #c0c0c0; BORDER-TOP-WIDTH: 1px; COLOR: #ff0000; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #c0c0c0; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #c0c0c0 1px solid" colSpan=3>Suppliers</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="BORDER-BOTTOM: #c0c0c0 1px solid; BORDER-LEFT: #c0c0c0 1px solid; BORDER-TOP-COLOR: #c0c0c0; BORDER-TOP-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-RIGHT: #c0c0c0 1px solid">Apple</TD><TD style="BORDER-BOTTOM: #c0c0c0 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #c0c0c0; BORDER-TOP-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #c0c0c0; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #c0c0c0 1px solid">A</TD><TD style="BORDER-BOTTOM: #c0c0c0 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #c0c0c0; BORDER-TOP-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #c0c0c0; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #c0c0c0 1px solid">B</TD><TD style="BORDER-BOTTOM: #c0c0c0 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #c0c0c0; BORDER-TOP-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #c0c0c0; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #c0c0c0 1px solid">D</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="BORDER-BOTTOM: #c0c0c0 1px solid; BORDER-LEFT: #c0c0c0 1px solid; BORDER-TOP-COLOR: #c0c0c0; BORDER-TOP-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-RIGHT: #c0c0c0 1px solid">Orange</TD><TD style="BORDER-BOTTOM: #c0c0c0 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #c0c0c0; BORDER-TOP-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #c0c0c0; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #c0c0c0 1px solid">A</TD><TD style="BORDER-BOTTOM: #c0c0c0 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #c0c0c0; BORDER-TOP-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #c0c0c0; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #c0c0c0 1px solid">B</TD><TD style="BORDER-BOTTOM: #c0c0c0 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #c0c0c0; BORDER-TOP-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #c0c0c0; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #c0c0c0 1px solid">C</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="BORDER-BOTTOM: #c0c0c0 1px solid; BORDER-LEFT: #c0c0c0 1px solid; BORDER-TOP-COLOR: #c0c0c0; BORDER-TOP-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-RIGHT: #c0c0c0 1px solid">Grape</TD><TD style="BORDER-BOTTOM: #c0c0c0 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #c0c0c0; BORDER-TOP-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #c0c0c0; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #c0c0c0 1px solid">A</TD><TD style="BORDER-BOTTOM: #c0c0c0 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #c0c0c0; BORDER-TOP-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #c0c0c0; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #c0c0c0 1px solid">C</TD><TD style="BORDER-BOTTOM: #c0c0c0 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #c0c0c0; BORDER-TOP-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #c0c0c0; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #c0c0c0 1px solid">D</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="BORDER-BOTTOM: #c0c0c0 1px solid; BORDER-LEFT: #c0c0c0 1px solid; BORDER-TOP-COLOR: #c0c0c0; BORDER-TOP-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-RIGHT: #c0c0c0 1px solid">Melon</TD><TD style="BORDER-BOTTOM: #c0c0c0 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #c0c0c0; BORDER-TOP-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #c0c0c0; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #c0c0c0 1px solid">B</TD><TD style="BORDER-BOTTOM: #c0c0c0 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #c0c0c0; BORDER-TOP-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #c0c0c0; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #c0c0c0 1px solid">C</TD><TD style="BORDER-BOTTOM: #c0c0c0 1px solid; BORDER-TOP-COLOR: #c0c0c0; BORDER-TOP-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #c0c0c0; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #c0c0c0 1px solid"></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="BORDER-BOTTOM: #c0c0c0 1px solid; BORDER-LEFT: #c0c0c0 1px solid; BORDER-TOP-COLOR: #c0c0c0; BORDER-TOP-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-RIGHT: #c0c0c0 1px solid">Banana</TD><TD style="BORDER-BOTTOM: #c0c0c0 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #c0c0c0; BORDER-TOP-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #c0c0c0; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #c0c0c0 1px solid">B</TD><TD style="BORDER-BOTTOM: #c0c0c0 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #c0c0c0; BORDER-TOP-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #c0c0c0; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #c0c0c0 1px solid">A</TD><TD style="BORDER-BOTTOM: #c0c0c0 1px solid; BORDER-TOP-COLOR: #c0c0c0; BORDER-TOP-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #c0c0c0; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #c0c0c0 1px solid"></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="COLOR: #ff0000; FONT-SIZE: 8pt; FONT-WEIGHT: bold">Search Result</TD><TD style="FONT-SIZE: 8pt"></TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px"></TD><TD style="FONT-SIZE: 8pt"></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="BORDER-BOTTOM-COLOR: #000000; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid" colSpan=2>Enter Supplier =</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">C</TD><TD style="FONT-SIZE: 8pt"></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD style="BORDER-BOTTOM: #969696 1px solid; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #969696; BORDER-TOP-WIDTH: 1px; COLOR: #ff0000; BORDER-RIGHT-COLOR: #969696; FONT-SIZE: 8pt; BORDER-LEFT-COLOR: #969696; BORDER-LEFT-WIDTH: 1px; FONT-WEIGHT: bold">Items</TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD style="BORDER-BOTTOM: #969696 1px solid; BORDER-LEFT: #969696 1px solid; BORDER-TOP-COLOR: #969696; BORDER-TOP-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-RIGHT: #969696 1px solid">Orange</TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD style="BORDER-BOTTOM: #969696 1px solid; BORDER-LEFT: #969696 1px solid; BORDER-TOP-COLOR: #969696; BORDER-TOP-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-RIGHT: #969696 1px solid">Grape</TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD style="BORDER-BOTTOM: #969696 1px solid; BORDER-LEFT: #969696 1px solid; BORDER-TOP-COLOR: #969696; BORDER-TOP-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-RIGHT: #969696 1px solid">Melon</TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD style="BORDER-BOTTOM: #969696 1px solid; BORDER-LEFT: #969696 1px solid; BORDER-TOP-COLOR: #969696; BORDER-TOP-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-RIGHT: #969696 1px solid"></TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">21</TD><TD style="BORDER-BOTTOM: #969696 1px solid; BORDER-LEFT: #969696 1px solid; BORDER-TOP-COLOR: #969696; BORDER-TOP-WIDTH: 1px; FONT-SIZE: 8pt; BORDER-RIGHT: #969696 1px solid"></TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD><TD style="FONT-SIZE: 8pt"></TD></TR></TBODY></TABLE>
cheers
Click to expand...

C17, control+shift+enter, not just enter, and copy down:
Code: 
=IF(ROWS($C$17:C17)<=COUNTIF($D$7:$F$11,$E$14),
    INDEX($C$7:$C$11,SMALL(IF($D$7:$F$11=$E$14,
     ROW($C$7:$C$11)-ROW($C$7)+1),ROWS($C$17:C17))),"")

See also your:
http://www.mrexcel.com/forum/showthread.php?p=2771966
 
Upvote 0
This will return the KN value that matches the largest value that is less than 1% in column E.

A named approach would use
Name: FilteredArray
RefersTo: = (Sheet1!$E$2:$E$16<0.01)*(Sheet1!$E$2:$E$16)

with this formula (in G1)
=INDEX(C:C, MATCH(LARGE(filteredArray,1), E:E, 0), 1)

or you could dispense with the name and use this CSE formula (in G3)

=INDEX(C:C, MATCH(LARGE((Sheet1!$E$2:$E$16<0.01)*(Sheet1!$E$2:$E$16),1), E:E, 0), 1)

Note the changed .95% value in row 13.
<table border=1 cellspacing=0>
<tr align="center" bgcolor=#A0A0A0><td width=25> <td width=25><b>A</b><td width=25><b>B</b><td width=25><b>C</b><td width=25><b>D</b><td width=25><b>E</b><td width=25><b>F</b><td width=25><b>G</b></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>1</b><td align="left" bgcolor=#FFFFFF>Confidence<td align="left" bgcolor=#FFFFFF>Ppk Goal<td align="left" bgcolor=#FFFFFF>Kn<td align="left" bgcolor=#FFFFFF>Required Ppk<td align="left" bgcolor=#FFFFFF>% of Change<td align="left" bgcolor=#FFFFFF><td align="right" bgcolor=#FFFFFF>42</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>2</b><td align="right" bgcolor=#FFFFFF>92%<td align="right" bgcolor=#FFFFFF>1.67<td align="right" bgcolor=#FFFFFF>9<td align="right" bgcolor=#FFFFFF>2.57<td align="right" bgcolor=#FFFFFF>10.05%<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>3</b><td align="right" bgcolor=#FFFFFF>92%<td align="right" bgcolor=#FFFFFF>1.67<td align="right" bgcolor=#FFFFFF>12<td align="right" bgcolor=#FFFFFF>2.38<td align="right" bgcolor=#FFFFFF>5.95%<td align="left" bgcolor=#FFFFFF><td align="right" bgcolor=#FFFFFF>42</tr>
<tr><td align="center" bgcolor=#A0A0A0><b>4</b><td align="right" bgcolor=#FFFFFF>92%<td align="right" bgcolor=#FFFFFF>1.67<td align="right" bgcolor=#FFFFFF>15<td align="right" bgcolor=#FFFFFF>2.27<td align="right" bgcolor=#FFFFFF>4.03%<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>5</b><td align="right" bgcolor=#FFFFFF>92%<td align="right" bgcolor=#FFFFFF>1.67<td align="right" bgcolor=#FFFFFF>18<td align="right" bgcolor=#FFFFFF>2.2<td align="right" bgcolor=#FFFFFF>2.95%<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>6</b><td align="right" bgcolor=#FFFFFF>92%<td align="right" bgcolor=#FFFFFF>1.67<td align="right" bgcolor=#FFFFFF>21<td align="right" bgcolor=#FFFFFF>2.15<td align="right" bgcolor=#FFFFFF>2.27%<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>7</b><td align="right" bgcolor=#FFFFFF>92%<td align="right" bgcolor=#FFFFFF>1.67<td align="right" bgcolor=#FFFFFF>24<td align="right" bgcolor=#FFFFFF>2.11<td align="right" bgcolor=#FFFFFF>1.82%<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>8</b><td align="right" bgcolor=#FFFFFF>92%<td align="right" bgcolor=#FFFFFF>1.67<td align="right" bgcolor=#FFFFFF>27<td align="right" bgcolor=#FFFFFF>2.07<td align="right" bgcolor=#FFFFFF>1.50%<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>9</b><td align="right" bgcolor=#FFFFFF>92%<td align="right" bgcolor=#FFFFFF>1.67<td align="right" bgcolor=#FFFFFF>30<td align="right" bgcolor=#FFFFFF>2.05<td align="right" bgcolor=#FFFFFF>1.26%<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>10</b><td align="right" bgcolor=#FFFFFF>92%<td align="right" bgcolor=#FFFFFF>1.67<td align="right" bgcolor=#FFFFFF>33<td align="right" bgcolor=#FFFFFF>2.03<td align="right" bgcolor=#FFFFFF>1.08%<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>11</b><td align="right" bgcolor=#FFFFFF>92%<td align="right" bgcolor=#FFFFFF>1.67<td align="right" bgcolor=#FFFFFF>36<td align="right" bgcolor=#FFFFFF>2.01<td align="right" bgcolor=#FFFFFF>0.94%<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>12</b><td align="right" bgcolor=#FFFFFF>92%<td align="right" bgcolor=#FFFFFF>1.67<td align="right" bgcolor=#FFFFFF>39<td align="right" bgcolor=#FFFFFF>1.99<td align="right" bgcolor=#FFFFFF>0.82%<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>13</b><td align="right" bgcolor=#FFFFFF>92%<td align="right" bgcolor=#FFFFFF>1.67<td align="right" bgcolor=#FFFFFF>42<td align="right" bgcolor=#FFFFFF>1.98<td align="right" bgcolor=#FFFFFF>0.95%<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>14</b><td align="right" bgcolor=#FFFFFF>92%<td align="right" bgcolor=#FFFFFF>1.67<td align="right" bgcolor=#FFFFFF>45<td align="right" bgcolor=#FFFFFF>1.96<td align="right" bgcolor=#FFFFFF>0.65%<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>15</b><td align="right" bgcolor=#FFFFFF>92%<td align="right" bgcolor=#FFFFFF>1.67<td align="right" bgcolor=#FFFFFF>48<td align="right" bgcolor=#FFFFFF>1.95<td align="right" bgcolor=#FFFFFF>0.59%<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
<tr><td align="center" bgcolor=#A0A0A0><b>16</b><td align="right" bgcolor=#FFFFFF>92%<td align="right" bgcolor=#FFFFFF>1.67<td align="right" bgcolor=#FFFFFF>51<td align="right" bgcolor=#FFFFFF>1.94<td align="right" bgcolor=#FFFFFF>0.54%<td align="left" bgcolor=#FFFFFF><td align="left" bgcolor=#FFFFFF></tr>
</table>
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
623
smozgur
smozgur
V
  • Solved
How to put a space in this table in VBA?
Replies
1
Views
501
VBAEXCELNew
V
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
921
epactheactor
E

Forum statistics

Threads
1,224,595
Messages
6,179,798
Members
452,943
Latest member
Newbie4296

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