Forum Discussion
Reverse H Look Up
Hi Every one
I have some data in the attached excel in Table 1 - I need to bring the place (Yellow Colored) for each achieve data from the data table 1.
Technically a "Reverse Hlook Up".
I tried with offset and match, index.
Any body can help me to set a formula for this. ?
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 #.
9 Replies
Sameer_Kuppanath_Sultan Perhaps the attached workbook answers your question. I used MMULT and introduced an array (1 row x 15 columns) filled with ones, to make it work.
It works, but only if either of the top 5 values falls in one place (i.e. one column). So, this is probably not a very useful exercise to begin with. Up to you to decide what to do with it.
Or, perhaps someone else has a better approach.
PS: Changed some numbers in various columns to test the formula and added some conditional formatting to visualise the top 5 values.
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 #.
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
