Index Match with count if returning 0. Formula problem?

[skyblue]

Would like to sort the person's names in $F$33:$F$36 by a particular class they have enrolled in $J$33 from among several classes (e.g. class1, class2 etc.)$G$33:$G$36 and have the list of names appear of people taking just that class.

The formula I'm using is:

=INDEX($F$33:$G$36,MATCH(0,COUNTIF($H$39:$H39,$F$33:$F$36)+($G$33:$G$36<>$J$33),0),COLUMN(F1))

So, from a list of names (Jenny, Jane, Fred etc.) it would just output the people taking class 1 at $H$39:$H39. That is the goal.

I think my formula is close but is returning a 0. If anyone could help with this, it would be most appreciated.

I'm using the Mac version of excel 2010.

 

[T. Valko]

Would like to sort the person's names in $F$33:$F$36 by a particular class they have enrolled in $J$33 from among several classes (e.g. class1, class2 etc.)$G$33:$G$36 and have the list of names appear of people taking just that class.

The formula I'm using is:

=INDEX($F$33:$G$36,MATCH(0,COUNTIF($H$39:$H39,$F$33:$F$36)+($G$33:$G$36<>$J$33),0),COLUMN(F1))

So, from a list of names (Jenny, Jane, Fred etc.) it would just output the people taking class 1 at $H$39:$H39. That is the goal.

I think my formula is close but is returning a 0. If anyone could help with this, it would be most appreciated.

I'm using the Mac version of excel 2010.

See if this is what you had in mind:

http://www.mrexcel.com/forum/showpost.php?p=2696961&postcount=3

 

[skyblue]

See if this is what you had in mind:

http://www.mrexcel.com/forum/showpost.php?p=2696961&postcount=3

What you posted is exactly the type of thing I'm looking for. However, I typed in the values exactly in the same columns/rows as your example, but the formula keeps coming up with #NAME?


Thanks for your help!

 

[T. Valko]

What you posted is exactly the type of thing I'm looking for. However, I typed in the values exactly in the same columns/rows as your example, but the formula keeps coming up with #NAME?


Thanks for your help!

Try this...

Book1
	F	G	H	I	J	K	L
32	_	_	_	_	Lookup	Count	Names
33	Name1	class1	_	_	class1	3	Name1
34	Name2	class2	_	_	_	_	Name3
35	Name3	class1	_	_	_	_	Name4
36	Name4	class1	_	_	_	_	_
Sheet1

Enter this formula in K33. This will return the count of records that meet the criteria.

=COUNTIF(G33:G36,J33)

Enter this array formula** in L33:

=IF(ROWS(L$33:L33)>K$33,"",INDEX(F:F,SMALL(IF(G$33:G$36=J$33,ROW(G$33:G$36)),ROWS(L$33:L33))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks

 

[skyblue]

Thanks for your help!

 

[T. Valko]

Thanks for your help!

You're welcome!