Index Match for multiple values with same results

[johnrlalor]

Hi,
I am trying to use the INDEX MATCH function to populate Columns E & F in the example below however I'm not having any luck, can anyone advise how to populate multiple values where the results are the same?
Thanks for any feedback / assistance

[TABLE="width: 500"]
<tbody>[TR]
[TD]Person[/TD]
[TD]Pos[/TD]
[TD][/TD]
[TD]Pos[/TD]
[TD]Name 1[/TD]
[TD]Name 2[/TD]
[/TR]
[TR]
[TD]Player A[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]Player A[/TD]
[TD]Player E[/TD]
[/TR]
[TR]
[TD]Player B[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]Player B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Player C[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]Player C[/TD]
[TD]Player F[/TD]
[/TR]
[TR]
[TD]Player D[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Player E[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Player F[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Player G[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 336"]
<colgroup><col span="2"><col><col span="2"><col></colgroup><tbody></tbody>[/TABLE]

 

[Aladin Akyurek]

Re: Help using Index Match for multiple values with same results

Book1
	A	B	C	D	E	F	G
1	Person	Pos		Pos	Name 1	Name 2
2	Player A	1		1	Player A	Player E
3	Player B	2		2	Player B
4	Player C	3		3	Player C	Player F
5	Player D	4		4	Player D	Player G
6	Player E	1
7	Player F	3
8	Player G	4
Sheet1

In E2 control+shift+enter, not just enter, copy across, and down:

=IFERROR(INDEX($A$2:$A$8,SMALL(IF($B$2:$B$8=$D2,ROW($A$2:$A$8)-ROW($A$2)+1),COLUMNS($E2:E2))),"")

If AGGREGATE is available on your system...

In E2 just enter, copy across, and down:

=IFERROR(INDEX($A$2:$A$8,AGGREGATE(15,6,(ROW($A$2:$A$8)-ROW($A$2)+1)/($B$2:$B$8=$D2),COLUMNS($E2:E2))),"")

 

[johnrlalor]

Re: Help using Index Match for multiple values with same results

If AGGREGATE is available on your system...

In E2 just enter, copy across, and down:

=IFERROR(INDEX($A$2:$A$8,AGGREGATE(15,6,(ROW($A$2:$A$8)-ROW($A$2)+1)/($B$2:$B$8=$D2),COLUMNS($E2:E2))),"")

That's worked perfectly, thanks so much for your help

 

[Aladin Akyurek]

Re: Help using Index Match for multiple values with same results

That's worked perfectly, thanks so much for your help

You are welcome.

 

[johnrlalor]

Re: Help using Index Match for multiple values with same results

If AGGREGATE is available on your system...

In E2 just enter, copy across, and down:

=IFERROR(INDEX($A$2:$A$8,AGGREGATE(15,6,(ROW($A$2:$A$8)-ROW($A$2)+1)/($B$2:$B$8=$D2),COLUMNS($E2:E2))),"")

One other item relating to this, If I have a possible 3rd value for a particular position what do I need to update in the code to get this to work?
Thanks

 

[Aladin Akyurek]

Re: Help using Index Match for multiple values with same results

Care to post a sample along with the desired output?

 

[johnrlalor]

Re: Help using Index Match for multiple values with same results

Care to post a sample along with the desired output?

Sorry!

A	B	C	D	E	F	G
Person	Pos	Pos	Name 1	Name 2
Player A	Player A	Player E
Player B	Player B
Player C	Player C	Player F	Player G
Player D	Player D
Player E
Player F
Player G

<tbody>
</tbody><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

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

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

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

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

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

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

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

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

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

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

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

[TD="align: right"]1[/TD]
[TD="align: right"][/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"]3[/TD]
[TD="align: right"][/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"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

 

[Aladin Akyurek]

Re: Help using Index Match for multiple values with same results

Book1
	B	C	D	E	F	G	H
1	Person	Pos		Pos	Name 1	Name 2
2	Player A	1		1	Player A	Player E
3	Player B	2		2	Player B
4	Player C	3		3	Player C	Player F	Player G
5	Player D	4		4	Player D
6	Player E	1
7	Player F	3
8	Player G	3
Sheet1

The formula which you already have does deliver exactly that output, i.e.:

In F2 enter, copy across, and down:

=IFERROR(INDEX($B$2:$B$8,AGGREGATE(15,6,(ROW($B$2:$B$8)-ROW($B$2)+1)/($C$2:$C$8=$E2),COLUMNS($F2:F2))),"")