Making a new list
 

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

Making a new list
 

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$3 0=$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!
================================================== ===

"cityfc" wrote in message
...
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

Making a new list
 

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

"RagDyeR" wrote:

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$3 0=$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!
================================================== ===

"cityfc" wrote in message
...
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

Making a new list
 

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
different numbers say 2 or 4 returning name in the list when either number
was found

thanks in advance


"cityfc" wrote:

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

"RagDyeR" wrote:

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$3 0=$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!
================================================== ===

"cityfc" wrote in message
...
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