=IF(ISERR(SMALL(IF(Group="A",ROW(INDIRECT("1:"&ROW S(Name)))),ROWS($1:1))),"",INDEX(Name,SMALL(IF(Gro up="A",ROW(INDIRECT("1:"&ROWS(Name)))),ROWS($1:1)) ))

ctrl+shift+enter, not just enter
copy down


"R G" wrote:

I've seen this question in an earlier post and was wondering if anyone could help as I can't make it work. I'm using Excel 97

Summary:
I would like to enter an equation that will return the first field (that is true for a certain condition) from a list. Then subsequent rows will return the next field (again according to the condition).
I can't use filters or pivots for this spread sheet.

Example:
I have a list as per:
Name Group
Bob A
Fred B
Matt B
Eric A
Dave A
Stew B
Fred A

I want to return all names that are in group A (condition: Group = A):
Name
Bob
Eric
Dave
Fred

or all names that are in group B (condition: Group = B):
Name
Fred
Matt
Stew

My original list will be edited and will need to automatically generate the required output lists. Once there are no more fields to
return (e.g. Fred in group A) then the formaula should return blank
cells.
Thanks in advance for any help/suggestions.