# Formula Help with Lists

G

#### Guest

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

B

#### 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

G

#### Guest

Thank You
That is perfect!

Biff said:
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

B

G

#### Guest

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

G

#### Guest

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?