Occurances Formula

[fari1]

<b>Sheet8</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Andy</td><td style="text-align:right; ">1</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Miree</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 >Alidf</td><td style="text-align:right; ">1</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >nimi</td><td style="text-align:right; ">3</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >lia</td><td style="text-align:right; ">1</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >teri</td><td style="text-align:right; ">2</td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4



hi, in my above example, i want to get all the values from column 1 against "1" from column 2 in column 3, which is

Andy
Alidf
lia

anyone can help with the formula? plz its urgent, thanking u in advance.

 

[villy]

Use autofilter then filter by "1"

 

[villy]

Or in Col C1:

=If(B1=1,A1,"")

then drag down

 

[Matty]

What version of Excel are you using?

Matty

 

[Matty]

If Excel 2007 or beyond:

=IFERROR(INDEX(A$1:A$6,SMALL(IF(B$1:B$6=1,ROW(A$1:A$6)-ROW(A$1)+1),ROWS(C$1:C1))),"")

If 2003 or before:

=IF(ROWS(C$1:C1)<=COUNTIF(B$1:B$6,1),INDEX(A$1:A$6,SMALL(IF(B$1:B$6=1,ROW(A$1:A$6)-ROW(A$1)+1),ROWS(C$1:C1))),"")

Matty

 

[fari1]

Hi Villy, cant use autofilter,
Matty i used your formula for excel 2007, but it is just giving one instance, not all the results

 

[Matty]

Sorry, I should have pointed out that the formulas I provided need committing with CTRL+SHIFT+ENTER due to their array nature, and can then be copied down.

Matty

 

[fari1]

thank u so much, its perfect