Source data as posted in A1:B1 down

To list all the names whose score is less than 2 in column D
In C1: =IF(AND(B10,B1<2),ROW(),"")
This is the criteria col

In D1: =IF(ROW()COUNT(D:D),"",INDEX(A:A,SMALL(D:D,ROW()) ))
Copy C1:D1 down to cover the max expected extent of source data.
Hide/Minimize col C. Col D will return the required results, all neatly
packed at the top

Repeat the construct likewise to extract as desired for names whose score is
greater than or equal to 6.

The criteria col would simply be: =IF(B1=6,ROW(),"")

Wave your success, click the YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Sasikiran" wrote:
I am looking for a formula to list out the names in a column only if it
meets the set criteria.

Column A Column B ColumnH Column I
ABC 4 BCD EFG
BCD 1.5 DEF FGH
CDE 5
DEF 1
EFG 6
FGH 8

Column A has the names and column B has their scores. I want to list all the
names whose score is less than 2 in column H and the list of names whose
score is greater than or equal to 6 in column I.

I could able to do that using a simple IF statement but it is leaving a
blank value if it is more than 2. Like wise in column I it is leaving a blank
cell if it less than 2.

=IF(AND(B10,B1<2),B1,"")

I wanted to get only the names without any blanks.

Please suggest me on this.

Thank you.