Index Match - Max + Descending Order

[UHD]

Hey guys,

I'm trying to use index match to return the top five values in the yellow highlighted cells. The data are in a table to the left. The correct answers for 1 and 2 are identified in columns I and K.

I don't know if this should be a 'max' value or something type of function as part of the index match. Thanks y'all!

[maras_mak]

Hi, test it.
I4 formula and copy right and down - answers for 1 - 5

Please Login or Register  to view this content.

Regards.

[GeoffW283]

I'm not sure that maras_mak is getting the expected result for answer-1.
Expected is BFCED, as far as I can see maras_mak gets BFDEE

Please try the following in J4 and copied down to J8:

Please Login or Register  to view this content.

Note that this is an array formula and must be committed with CTRL-SHIFT-ENTER
I believe this gives the expected results for answer-1.

In L4 a slightly different formula (only because of the intervening "answer" column):

Please Login or Register  to view this content.

Note that this is an array formula and must be committed with CTRL-SHIFT-ENTER

For answer-2, expected is FCBED, I get FCBDE. Activity types D and E both have a value of 16 so does this discrepancy in order matter?

The attached workbook implements the above.