List if

[Sasikiran]

Dear,

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(B1>0,B1<2),B1,"")

I wanted to get only the names without any blanks.

Please suggest me on this.

Thank you.

 

[Max]

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(B1>0,B1<2),ROW(),"")
This is the criteria col

In D1: =IF(ROW()>COUNT(D),"",INDEX(A:A,SMALL(D,ROW())))
Copy C11 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

 

[Shane Devenshire]

Hi,

Try this array formula in H1:

=INDEX(A$1:A$6,SMALL(IF(B$1:B$6<2,ROW(B$1:B$6),""),ROW()))

array - you must press Shift+Ctrl+Enter to enter the formula. In I1:

=INDEX(A$1:A$6,LARGE(IF(B$1:B$6>=6,ROW(B$1:B$6),""),ROW()))

Drag these formulas down as far as necessary. They will return #NUM! errors
after the last item is found, to prevent that you can get fancier with:

=IF(ROW()<=COUNTIF($B$1:$B$6,"<2"),INDEX(A$1:A$6,SMALL(IF(B$1:B$6<2,ROW(B$1:B$6),""),ROW())),"")

and

=IF(ROW()<=COUNTIF($B$1:$B$6,">=6"),INDEX(A$1:A$6,LARGE(IF(B$1:B$6>=6,ROW(B$1:B$6),""),ROW())),"")

both array entered. These can be copied down as far as you wish.

 

[Sasikiran]

Hi Shane,

The fancier formula isn't working according to my need.

I've used the ISERROR function to remove the #NUM! errors.

Thanks so much.

=(IF(ISERROR(INDEX(A$1:A$6,SMALL(IF(B$1:B$6<2,ROW(B$1:B$6),""),ROW()))),"",INDEX(A$1:A$6,SMALL(IF(B$1:B$6<2,ROW(B$1:B$6),""),ROW()))))