Index Match Return Multiple Matches

Alewis2423

New Member
Joined
Mar 6, 2013
Messages
3
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


<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

raj2206

Board Regular
Joined
Jul 23, 2012
Messages
213
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
 

Watch MrExcel Video

Forum statistics

Threads
1,095,978
Messages
5,447,670
Members
405,462
Latest member
est1989

This Week's Hot Topics

Top