software

I am using the following formula to look up a value (in column A) that has
multiple corresponding values in column B. However, when I insert an
additional row at the top, the formula doesn't work anymore. What can I do?

=INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1 :$A$7)),ROW(1:1)),2)

Thanks.

Hi!

Try this:

=INDEX($B$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1 :$A$7)-ROW(A$1) 1),ROWS($1:1)))

Biff

quot;Wkquot; gt; wrote in message
...
gt;I am using the following formula to look up a value (in column A) that has
gt; multiple corresponding values in column B. However, when I insert an
gt; additional row at the top, the formula doesn't work anymore. What can I
gt; do?
gt;
gt; =INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1 :$A$7)),ROW(1:1)),2)
gt;
gt; Thanks.