I have the following table (simplified):
I want to sum the top 4 values for the year 2002. However, the year whose values I want to retrieve can change based on a dropdown list. So, I might want 2004 or 2006 values, for example. In other words, it should look for the column whose header is similar to the value in cell A1.
Thanks.
CodePudding user response：
This will get you the top four values in 4 different cells.
=Large(INDEX($C$3:$D$9,,match($H$4,$C$2:$D$2,0)),F5)
or a dynamic list might be better... where L4 represents the number of top values (in your case 4).
=arrayformula(Large(INDEX($C$3:$D$9,,match($H$4,$C$2:$D$2,0)),SEQUENCE(L4,1,1,1)))
If you wanted to sum them up, you could change the above formula to be:
=sum(arrayformula(Large(INDEX($C$3:$D$9,,match($H$4,$C$2:$D$2,0)),{1,2,3,4})))