look up formulas

[Jill_]

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?

[Domenic]

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 <9F01F81B-EF0A-48BD-923A-93C579678815@microsoft.com>,
Jill_ <Jill@discussions.microsoft.com> 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?

[Jill_]

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 <9F01F81B-EF0A-48BD-923A-93C579678815@microsoft.com>,
> Jill_ <Jill@discussions.microsoft.com> 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?
>