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

  • Thread starter Thread starter worksheet functions
  • Start date 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))
 
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...
 
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
 
Back
Top