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

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

<tbody>
</tbody>

<colgroup><col span="2"><col><col span="2"><col></colgroup><tbody></tbody>

 

[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
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

<tbody>
</tbody><thead>
</thead><tbody>
</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))),"")