Look up to return multiple corresponding values

G

Guest

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.
 
B

Biff

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Lookup one value and return multiple corresponding values 1
How to look up a value in a list and return multiple corresponding 3
External reference not working? 5
find largest values, then return corresponding row values. 7
VLOOKUP/MATCH Query - quite complex....but interesting. 3
Returning multiple corresponding values using lookup in a list 2
Look up two values in different columns and return matching value 5
How to look up a value in a list and return multiple corresponding 2

Top