Returning multiple corresponding values using lookup in a list

I am using the following formula to lookup information that is in Column A in
order to return the value in Column B. There is duplicate information in
Column A, but different corresponding information in Column B. The following
formula works, but when I try to add a row at the top (above the list of
information), the formula no longer works. Can you help?

=INDEX($A$1:$C$40,SMALL(IF($A$1:$A$40=$A$50,ROW($A$1:$A$40)),ROW(1:1)),2))

If, for example, you enter the formula in B50 and copy it down the
column, try...

=INDEX($A$1:$C$40,SMALL(IF($A$1:$A$40=$A$50,ROW($A$1:$A$40)-ROW($A$1)+1),
ROWS($B$50:B50)),2)

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article <[email protected]>,
"Wk" <[email protected]> wrote:

> I am using the following formula to lookup information that is in Column A in
> order to return the value in Column B. There is duplicate information in
> Column A, but different corresponding information in Column B. The following
> formula works, but when I try to add a row at the top (above the list of
> information), the formula no longer works. Can you help?
>
> =INDEX($A$1:$C$40,SMALL(IF($A$1:$A$40=$A$50,ROW($A$1:$A$40)),ROW(1:1)),2))

That worked. Thanks!!!

"Domenic" wrote:

> If, for example, you enter the formula in B50 and copy it down the
> column, try...
>
> =INDEX($A$1:$C$40,SMALL(IF($A$1:$A$40=$A$50,ROW($A$1:$A$40)-ROW($A$1)+1),
> ROWS($B$50:B50)),2)
>
> ....confirmed with CONTROL+SHIFT+ENTER.
>
> Hope this helps!
>
> In article <[email protected]>,
> "Wk" <[email protected]> wrote:
>
> > I am using the following formula to lookup information that is in Column A in
> > order to return the value in Column B. There is duplicate information in
> > Column A, but different corresponding information in Column B. The following
> > formula works, but when I try to add a row at the top (above the list of
> > information), the formula no longer works. Can you help?
> >
> > =INDEX($A$1:$C$40,SMALL(IF($A$1:$A$40=$A$50,ROW($A$1:$A$40)),ROW(1:1)),2))
>