Hi,

I need help with an array formula (CSE) or lookup that will return all
values the lookup finds not just the first. Here is an example of my data,
as u can see there are dups.

brand1 blue
brand1 green
brand1 red
brand2 yellow
brand2 blue
brand2 brown

I'd like to be able to drag the formula down and have it return all values
for 'brand1' not just the 1st one it finds. Been having difficulty
manipulating vlookups & index(match) to accomplish this.

Thanks for your help

MattR

In the menu select Filter Auto Filter (make sure you have a header in your
data)

If you prefered the formula method then try this:

=IF(ISERR(SMALL(IF($C$2:$C$7=$E$1,ROW(INDIRECT("1: "&ROWS($D$2:$D$7)))),ROWS($1:1))),"",INDEX($D$2:$D $7,SMALL(IF($C$2:$C$7=$E$1,ROW(INDIRECT("1:"&ROWS( $D$2:$D$7)))),ROWS($1:1))))

ctrl+shift+enter (not just enter)
copy down




"Matt R" wrote:

Hi,

I need help with an array formula (CSE) or lookup that will return all
values the lookup finds not just the first. Here is an example of my data,
as u can see there are dups.

brand1 blue
brand1 green
brand1 red
brand2 yellow
brand2 blue
brand2 brown

I'd like to be able to drag the formula down and have it return all values
for 'brand1' not just the 1st one it finds. Been having difficulty
manipulating vlookups & index(match) to accomplish this.

Thanks for your help

MattR

I forgot to mention...assuming E1 is your citeria


"Teethless mama" wrote:

In the menu select Filter Auto Filter (make sure you have a header in your
data)

If you prefered the formula method then try this:

=IF(ISERR(SMALL(IF($C$2:$C$7=$E$1,ROW(INDIRECT("1: "&ROWS($D$2:$D$7)))),ROWS($1:1))),"",INDEX($D$2:$D $7,SMALL(IF($C$2:$C$7=$E$1,ROW(INDIRECT("1:"&ROWS( $D$2:$D$7)))),ROWS($1:1))))

ctrl+shift+enter (not just enter)
copy down




"Matt R" wrote:

Hi,

I need help with an array formula (CSE) or lookup that will return all
values the lookup finds not just the first. Here is an example of my data,
as u can see there are dups.

brand1 blue
brand1 green
brand1 red
brand2 yellow
brand2 blue
brand2 brown

I'd like to be able to drag the formula down and have it return all values
for 'brand1' not just the 1st one it finds. Been having difficulty
manipulating vlookups & index(match) to accomplish this.

Thanks for your help

MattR