How to look up a value in a list and return multiple corresponding

  • Thread starter worksheet functions
  • Start date
W

worksheet functions

I can make the formula look up anumber but i need it to look for a specific
word. What can i replace "SMALL" with to have the formula bring all intances
of the word "Active" =SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1))
 
L

Luke M

Copy the formula downward. This will cause the final arguement, ROW(1:1) to
change, causing the formula to pull the 1st isntance, then 2nd, then 3rd,
etc...
 
B

Bernie Deitrick

Array enter, using Ctrl-Shift-Enter

=IF(COUNTIF($A$1:$A$7,$A$10)>=ROWS($A$1:A1),INDEX(A$1:A$7,LARGE(($A$1:$A$7=$A$10)*ROW($A$1:$A$7),COUNTIF($A$1:$A$7,$A$10)-(ROWS($C$12:C12)-1))),"")

with Active in cell A10. Change $C$12:C12 to reflect the cell where you are entering this: D4 would
be $D$4:D4

Then copy down and over...


HTH,
Bernie
MS Excel MVP
 

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

Look up to return multiple corresponding values 1
Lookup one value and return multiple corresponding values 1
How to look up a value in a list and return multiple corresponding 2
External reference not working? 5
How to look up a value in a list and return multiple corresponding values 7
Look up two values in different columns and return matching value 5
Returning multiple corresponding values using lookup in a list 2
Scrabble Value calculation for Welsh words 0

Top