index/match

[Guest]

I know i should be able to figure this out from the threads, but i just cant
get it to work...

I have a list of schools in one column, and a list of the type of the
school, (HS, MS ES) . I want to return three seperate lists, one for each
school type. Thanks!

 

[Bob Phillips]

Assuming that the schools are in A1:A100, types in B1:B100

In C1: =IF(B1="HS",ROW(),""), copy down to C100
In D1: =SMALL(IF($C$1:$C$100="","",ROW($C$1:$C$100)),ROW(C1)),which is an
array formula, so commit with Ctrl-SHift-Enter, and copy down to D100
In E1: =IF(ISNUMBER(D1),INDIRECT("A"&D1),"") and copy down to E100

The last is the list of schoold of type HS. Repeat for the others.

 

[Guest]

is there any way to do this in one step with index/match, to reduce the
number of calcs needed?

 

[Guest]

Hi,

This may not be exactly wjat you want but how about using the Auto Filter
function.

Regards,

Ashish Mathur