Making a new list

C

cityfc

hi
i have 2 columns, column a has a list of names, column b has a number 1-8 in
it.
what i would like is on another sheet a list of the names that has a 2 in
column d
 
R

RagDyeR

With original list on Sheet1, enter this *array* formula in Sheet2, and
enter the number you're looking up into D1 of Sheet2:

=INDEX(Sheet1!$A$1:$A$30,SMALL(IF(Sheet1!$B$1:$B$30=$D$1,ROW($1:$30)),ROW(1:
1)))

--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

Copy down for as many rows as you anticipate the number of names to be
returned.

When you run out of names that match the lookup number in D1, you'll get
#NUM! errors.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

hi
i have 2 columns, column a has a list of names, column b has a number 1-8 in
it.
what i would like is on another sheet a list of the names that has a 2 in
column d
 
C

cityfc

Thanks RagDyeR but it returns an error also can this formula look for 2
different numbers say 2 or 4 returning name in the list when either number
was found

thanks in advance
 
C

cityfc

sorry RagDyeR there is no error it was me there was a space in formula that
returned the error also can this formula look for 2 different numbers say 2
or 4 returning name in the list when either number was found

thanks in advance
 
Top