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 i don't understand what you are trying to do here. There is no 'place' in your table. Also your 'Achieve' column which appears to be a 'rank' is incorrect in that it does not account for multiple entries with the same value (unless that is what you wanted?). A better 'rank' formula would be to use LARGE i.e. =LARGE(B3:B17,H7)
In order to get a column ranked based on a different column you could use a formula like the following if you a have the newest functions in Excel:
=INDEX(SORT($B$3:$C$17,1,-1),H7,2)where I return column 2 from the range B3:C17. If you wanted a different column you would expand the range and adjust that last index from 2 to which column you want.
mtarler Hi
I think you did nt get my question.
First- The large formula, I cant use, since it is not flushing out duplicate values.
Lately- I need to bring the places (College, hotel etc.. ) into the cell which is colored yellow, representing the number in previous column (Achieve) from Data table. , not the number!!
Please check the attached