INDEX MATCH vs SUMIFS Query

[sgm2]

Hi I'm trying to convert a SUMIFS query into an INDEX MATCH function for golf results. The only columns to consider are Year, Player Name and Position. If it was only numeric a SUMIFS function would do the job and if it was only for one year the INDEX(X:X,MATCH(A1,Y:Y,0)) - or a simple VLOOKUP function would work but the fact that the same names comes up in different years is stumping me. I need to add in an IF statement. For Example you might have:

2021 Rory McIlroy T5
2020 Rory McIlroy 8
2019 Rory McIlroy MC

Any help is appreciated.

 

[RoryA]

You could use FILTER since you have 365.

 

[Oaktree]

Welcome to the board.

Maybe something like this?

=TEXTJOIN(", ",TRUE,IF(B1:B4="Rory McIlroy",C1:C4,""))

 

[sgm2]

You could use FILTER since you have 365.

Thanks for the response. I'm not sure I follow though...

I have a list of results for a particular competition for say 12 years. From that list i'm trying to pull the results to a matrix. If the list is say:

2021 Rory McIlroy T5
2021 Jon Rahm 8
2020 Jon Rahm 1
2020 Rory McIlroy T20

I'm looking for a matrix showing:

Rory McIlroy T5 T20
Jon Rahm 8 1

Its easy using a lookup function for each separate year but I'm sure there is a more efficient way considering i'll be looking to update the sheet every year.

Thanks again.

 

[sgm2]

Welcome to the board.

Maybe something like this?

=TEXTJOIN(", ",TRUE,IF(B1:B4="Rory McIlroy",C1:C4,""))

Thanks for the response.

I'm not sure this would work (see above post) there are multiple rows with "Rory McIlroy" for the different years so how would that function separate them. The easiest way to explain is if there were no text and it was just numeric (and say you replaced MC with 500) a simple SUMIFS(C1:C4,A1:A4,2021,B1:B4,"Rory McIlroy")

 

[Oaktree]

My previous suggestion would concatenate across years. If you want the years in separate columns, it sounds like this might be what you're looking for, where it uses the CONCATENATEX DAX function within a pivot table.

See this for more:

Pivot Table With Text in Values Area

Is there a way to show Text in the Values area of a pivot table?

www.mrexcel.com

 

[sgm2]

My previous suggestion would concatenate across years. If you want the years in separate columns, it sounds like this might be what you're looking for, where it uses the CONCATENATEX DAX function within a pivot table.

See this for more:

Pivot Table With Text in Values Area

Is there a way to show Text in the Values area of a pivot table?

www.mrexcel.com

Hi, I appreciate the time and help but i'm not quite getting a solution. Would it be possible to email you my sheet for more clarity. I'm sure there will be a simple solution by incorporating an IF statement to an INDEX MATCH.

 

[Oaktree]

Book3
	A	B	C	D	E	F	G	H
1	Year	Name	Finish
2	2021	Rory McIlroy	T5			Formula Result
3	2021	Jon Rahm	8		Rory McIlroy	T5, T20
4	2020	Jon Rahm	1		Jon Rahm	8, 1
5	2020	Rory McIlroy	T20
6
7
8	Year	Name	Finish		Results	Column Labels
9	2021	Rory McIlroy	T5		Row Labels	2020	2021	Grand Total
10	2021	Jon Rahm	8		Jon Rahm	1	8	8, 1
11	2020	Jon Rahm	1		Rory McIlroy	T20	T5	T5, T20
12	2020	Rory McIlroy	T20
13
Sheet1

Cell Formulas
Range	Formula
F3:F4	F3	=TEXTJOIN(", ",TRUE,IF($B$2:$B$5=E3,$C$2:$C$5,""))

A1:C5 has a normal range of cells, E3:E4 have the list of golfers, and F3:F4 has the result of the TEXTJOIN formula.

A8:C12 has the same data, but input as a table. E8:H11 has the output you'd get per the CONCATENATEX DAX formula within a pivot table per the link above.

If neither of those are what you're looking for, please post a sample of what you would like the result to look like.