I need a forumula that will give me mulitipal answers.

I have a constant value that is looking that value up in a chart and from
that constant value I need it to return the corresponding names.

ie #01001

Chart
01001 name 1
00001 name 2
11000 name 3
01001 name 4
01001 name 5

I am needing it to list NAME 1, NAME 4 and NAME 5

How do I write this?

Assuming that A2:B6 contains the data, try the following formula, which
needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

E2, copied down:

=IF(ROWS(E$2:E2)<=COUNTIF($A$2:$A$6,$D$2),INDEX($B $2:$B$6,SMALL(IF($A$2:$
A$6=$D$2,ROW($A$2:$A$6)-ROW($A$2)+1),ROWS(E$2:E2))),"")

....where D2 contains the value of interest, such as 01001.

Hope this helps!

In article ,
Jill_ wrote:

I need a forumula that will give me mulitipal answers.

I have a constant value that is looking that value up in a chart and from
that constant value I need it to return the corresponding names.

ie #01001

Chart
01001 name 1
00001 name 2
11000 name 3
01001 name 4
01001 name 5

I am needing it to list NAME 1, NAME 4 and NAME 5

How do I write this?

Thanks Domenic, you are awesome! That helped out a lot!

"Domenic" wrote:

Assuming that A2:B6 contains the data, try the following formula, which
needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

E2, copied down:

=IF(ROWS(E$2:E2)<=COUNTIF($A$2:$A$6,$D$2),INDEX($B $2:$B$6,SMALL(IF($A$2:$
A$6=$D$2,ROW($A$2:$A$6)-ROW($A$2)+1),ROWS(E$2:E2))),"")

....where D2 contains the value of interest, such as 01001.

Hope this helps!

In article ,
Jill_ wrote:

I need a forumula that will give me mulitipal answers.

I have a constant value that is looking that value up in a chart and from
that constant value I need it to return the corresponding names.

ie #01001

Chart
01001 name 1
00001 name 2
11000 name 3
01001 name 4
01001 name 5

I am needing it to list NAME 1, NAME 4 and NAME 5

How do I write this?