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

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

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

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...
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"worksheet functions" wrote:

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

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

sample before/after

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"worksheet functions" <worksheet wrote
in message ...
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) )

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

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),COU NTIF($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


"worksheet functions" <worksheet wrote in message
...
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) )