Forum Discussion
Reverse H Look Up
Sameer_Kuppanath_Sultan My example was merely to demonstrate what is possible. Unfortunately, the MMULT function is not the most straightforward one. You can read all about it in the Excel help screens or in other on-line resources. These can explain the function much better than I can.
But, in summary, MMULT works with two arrays. One is what I called Array1, the other is an array that is "created" by the second part (i.e. what comes after "Array1". Once you understand how MMULT works you can break-down that "calculation" of the second array to find it creates an array (15 rows x 5 columns) with zeros in most cells. Only cells that match the selected value from column I will have a 1.
And that is where the weakness comes in. If you expect some of the top 5 values to occur in more than one column, you can throw this whole approach away, as it will only pick-up the first column in which the selected value occurs.
And, yes, if you add rows to your table, the Array1 (named range) needs to become 16 columns wide. If you are a recent Excel version with the new DA functions you can create a sequence of ones based on the number of rows in your table (<count>) and reference that sequence with its first cell followed by a #.
Sameer_Kuppanath_Sultan Well done! Consider to change the SEQUENCE formula to:
=SEQUENCE(1,COUNT(Table2[College]),1,0)Just think it's neater. But that's personal.
Riny_van_Eekelen PERFECT!!
Same logic but without MMULT and DA
=INDEX(Place, AGGREGATE( 15,6, 1/((Table1=I7)*COLUMN(Table1)>0)*(Table1=I7)*COLUMN(Table1), 1)- INDEX(COLUMN(Table1),1)+1 )It's here
