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 #.
Riny_van_Eekelen Hi- thanks for your input.
Two things.
1. I did not understand this formula MMIUT- I have seen this first time.
"(MMULT(Array1,IFERROR(FIND(TRUE,I7=Table1),0))<>0)*COLUMN(),0)"
2. Nevertheless, I have got another confusion on- If we add one more line item as "16th" item,
and I have converted the table to Excel official Table, I have to update the so called "Array 1" as adding 1 to next column for working with this formula, right?
I think that is too inconvenient that, every time to add "1" in such a way?
Please comment and advise.!
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 #.
Thanks a lot- It worked. Please look into the attached sheet. I MADE IT!!! 🙂
As you said, it will not pick the second result, but its ok, this is what I need!!
You saved my day...!
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!!