Lookup into tbl w/ duplicate values
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 |
Lookup into tbl w/ duplicate values
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 |
Lookup into tbl w/ duplicate values
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 |
All times are GMT +1. The time now is 08:13 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com