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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

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
 

Forum statistics

Threads
1,089,587
Messages
5,409,153
Members
403,254
Latest member
Saiyan_8

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top