Formula Help with Lists

[TN.Jim]

I need to be able to pull a list from series of data without sorting or
filtering...
Example:
A B
1 Jim Boy
2 Ann Girl
3 John Boy
4 Mike Boy
5 Tina Girl
6 Matt Boy
7 Beth Girl

Then if a user selects "Boy" in cell A8, I want
A9 = "Jim"
A10 = "John"
A11 = "Mike"
A12 = "Matt"
I can use =index(A1:A7,match(A8,B1:B7,0)) to get "Jim" in A9 but how can I
get
John in A10 etc?

The actual list is much more complicated but I hope that this gives an idea
of what I need to do.

Thanks for the help!

Jim

 

[Biff]

Hi!

Try this:

Entered in A9 as an array using the key combo of CTRL,SHIFT,ENTER:

=IF(ROWS($1:1)<=COUNTIF(B$1:B$7,A$8),INDEX(A$1:A$7,SMALL(IF(B$1:B$7=A$8,(ROW(A$1:A$7)-ROW(A$1)+1)),ROW(A1))),"")

Copy down until you get blank cells.

Biff

 

[TN.Jim]

Thank You
That is perfect!

Hi!

Try this:

Entered in A9 as an array using the key combo of CTRL,SHIFT,ENTER:

=IF(ROWS($1:1)<=COUNTIF(B$1:B$7,A$8),INDEX(A$1:A$7,SMALL(IF(B$1:B$7=A$8,(ROW(A$1:A$7)-ROW(A$1)+1)),ROW(A1))),"")

Copy down until you get blank cells.

Biff

 

[Biff]

You're welcome!

Biff

Thank You
That is perfect!

 

[Ashish Mathur]

Hi,

There is another way to do this. Array entert the following formula
(Ctrl+Shift+Enter)

Enter Boy in cell A10 instead of cell A8.

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

Hope this helps

 

[kate]

Hello I have been trying to find help on filtering a list and have found this
posting which is great and helps however within my list I want it to look up
two sections as follows:
(IF(ROWS('Data extract table'!BM340:BM340)<=COUNTIF('Data extract
table'!BM$3:BM$56000,1),INDEX('Data extract
table'!BM$3:BM$56000,SMALL(IF('Data extract table'!BM$3:BM$56000=1,(ROW('Data
extract table'!BM$3:BM$56000)-ROW('Data extract table'!BM$3)+1)),ROW('Data
extract table'!BM340))),IF(ROWS('Data extract
table'!BM340:BM340)<=COUNTIF('Data extract
table'!BM$3:BM$56000,2),INDEX('Data extract
table'!BM$3:BM$56000,SMALL(IF('Data extract table'!BM$3:BM$56000=2,(ROW('Data
extract table'!BM$3:BM$56000)-ROW('Data extract table'!BM$3)+1)),ROW('Data
extract table'!BM340))))))

It is providing me with the first list from patient type 1 but the patient
type 2 list that I just wish to continue below it is coming up #num????
what am i doing wrong please?