Luke M, how do you find fruit?

[Lawrence Dodge]

HI Luke--

Thanks for you forensic analysis of the fruit / vegetable question. My question is this: what if you wanted to list the fruits instead of the vegetables? My colleague and I tried changing the criteria and nothing, tried making the < ROWS into > ROWS and still nothing? Could you please explain what we are not getting?

Thanks

Lawrence

 

[Hui]

Lawrance


If you Enter Vegetables in the Criteria cell D2


Change the formula in E2 to:


E2: =IF(COUNTIF(A:A,$D$2)<ROWS($E$2:E2),"",INDEX(B:B,SMALL(IF($A$2:$A$10 <>$D$2,ROW($A$2:$A$10)),ROW(A1))))

Ctrl Shift Enter


Then Copy E2 to E3:E10


This will list everything except Vegetables


Of course with the original formula just enter Fruit into the Criteria Cell D2

 

[Lawrence Dodge]

Hi Hui--

thanks for the help. We tried to enter Fruit into the Criteria Cell D2 and it didn't work. Any thoughts?

LCD

 

[Luke M]

Strange...is calculation mode set to Manual by chance? Is the formula displaying an error, blank, or wrong data?