Returning multiple corresponding values using lookup in a list

W

Wk

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

Wk

That worked. Thanks!!!

Domenic said:
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!
Click to expand...
 
Top