Index Match Return Multiple Matches


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


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"


Board Regular
Jul 23, 2012
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:


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

Latest member

This Week's Hot Topics