Index Match Return Multiple Matches

[Alewis2423]

Hi Guys,

I'm trying to run an index match function to return multiple matches, but I can't seem to make it return more than just the first value. For example, I'm trying to run a formula that will look for a particular product and then return a customer id number every time it sees that product (without repeating). It'd be extra awesome if there was a way to have it input all these values without empty cells or #N/A between occurrences.

In the hypothetical example below, I'd want the formula to return the 3 different ids for customers who get the product HUGE XXL - Bigtime. The formula I had used was =index($A$1:$A$8,(match("HUGE XXL - Bigtime",$D$1:$D$8,0)). This only worked for the 1st customer (1577787 --> Jon) but not the others. I'd like to have it so that the formula ignores the other values and only populates ids for customers that have the HUGE XXL - Bigtime (Jon, Tim, and Dan). Any ideas that would make it populate the other ids? Thanks so much!

id name product_id product_name
1570574 Jim 112567 Bigtime
1574649 Joe 112564 Littletime
1577787 Jon 113569 HUGE XXL - Bigtime
1578017 Jan 112567 Bigtime
1580602 Tim 113569 HUGE XXL - Bigtime
1580609 Bob 112564 Littletime
1580614 Dan 113569 HUGE XXL - Bigtime

[TABLE="width: 439"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[raj2206]

Hey Thanks for writing!

Say your data (in your post) is in A1:D8, now in any another column paste all the unique product_name (in your case Bigtime, Littletime, HUGE XXL - Bigtime) say I have these names in F1:F3, then in cell G1 paste the following formula and drag it across till the maximum count of the product_names and drag the formula down as well:

=IF(COLUMNS($F$1:F$1)<=COUNTIF($D$2:$D$8,$F1),(INDEX($A$2:$A$8,LARGE((ROW($D$2:$D$8)-ROW($D$2)+1)*($F1=$D$2:$D$8),COLUMNS($F$1:F$1)))),"")

Make sure you press Control+Shift+Enter while entering the formula, since it is an array operation.

Hope this Helps!

Thanks/ Raj