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.
>Solution :
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})))