Index Match to find multiple matches in a column

[arsmith646]

Hello,

I have figured out how to compare and search two columns to return a corresponding value, but there are multiple values that may match, and I am unable to collect the multiple values. The headings I am searching are below:

Full Name (this is the column I am searching - Column A); Activity Number (the value(s) I need returned - Column B); Pass2 (the column I am using to search for the name in Column A - Column C); Then the remaining columns need to return the corresponding activity or activities from the search criteria in Column C.

I'm trying to avoid having to write in VBA using a For Loop and am wondering if there is any way to collect this data using the function below:

=INDEX(B:B,MATCH(C2,A:A,0))

This will only return a single matching activity from Column B.

Please let me know if I'm unclear, and I want to thank you for your help in advance.

 

[Eric W]

Here's one way.

Excel 2010

	A	B	C	D	E	F
Full Name	Activity Number	Pass2	Activity 1	Activity 2	Activity 3
Joe Blow	John Doe
John Doe	Max Power
Jane Roe
Joe Blow
Max Power
John Doe
Max Power
John Doe
Mary Smith

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]{=IFERROR(INDEX($B:$B,SMALL(IF($A$2:$A$10=$C2,ROW($A$2:$A$10),9E+99),COLUMN(D2)-COLUMN($D$2)+1)),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Put the formula in D2, confirm with Control-Shift-Enter. Then copy D2 and paste it in the cells as far to the right as needed, and as far down the column as needed. If I've created the sample sheet correctly, you should have what you need.

Let me know how it works.

 

[arsmith646]

Thank you so much! It works wonders. I was working on the macro for it, and it was taking forever. I greatly appreciate the help.

 

[Eric W]

Happy to help! Thanks for the feedback.

 

[arsmith646]

Hello again,

I was hoping you could help me if I needed to match another two columns to return a value matching criteria. In other words, in addition to the members names matching, I would also have to match the activity numbers to pull out the amount someone paid for that activity. Like with these headers:

PassMember1 Activity AmountPaid Activity2 Enrollee TotPaid

I would need to find the PassMember1 that matches Enrolee as well as the Activity match Activity2 to return the TotPaid into the AmountPaid column. Does that make sense?

Thank you again for all your help!