Returning multiple corresponding values using lookup in a list

[Guest]

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))

 

[Domenic]

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!

 

[Guest]

That worked. Thanks!!!

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!