Forum Discussion
Solved
Using INDEX and Match when the column in the second MATCH argument changes
Hello, I am using Office 365 and I this is my first post! I am using this formula and I need to change it
=INDEX(Data!$B$5:$B$144, MATCH(I7,Data!$I$5:$I$144, 0))The formula pulls the appropriate name using B5:B144
It pulls the appropriate revenue value from I7
It currently pulls the corresponding revenue from I5:I144
However, I changed column I, where I7 resides, to a drop down where the user selects a month Jan-Dec. The drop down is in cell I6. The issue is that the formula above is pointing at column I which is the column for January. When I change Jan to Feb, I get an error message for the formula above.
I believe I need to change only this portion I5:I144 but I can't figure out how to change it so that it sees the drop down in I6 and then looks for that column in the Data tab.
Thanks in advance for any help
I see that you have SEQUENCE in that second formula so that tells me you have Excel 365 (I believe)
In that case you can AND SHOULD replace both formulas with the new available formulas.
For example you can:
=TAKE(SORT(FILTER(Data!$G$5:$T$144, I$6=Data!$G$4:Data!$T$4),,-1),10)
So basically filter the whole table (g5:t144) based on the column header = this column header, and then sort it and then finally take the first 10 values.
For the other column I would do something similar:
=TAKE(SORT(Data!$B$5:$T$144, XMATCH(C$6,Data!$B$4:Data!$T$4),-1),10,1)
in this case sort the whole tables of data based on the column that matches the header and then take the 10 rows and only the 1st column
5 Replies
I see that you have SEQUENCE in that second formula so that tells me you have Excel 365 (I believe)
In that case you can AND SHOULD replace both formulas with the new available formulas.
For example you can:
=TAKE(SORT(FILTER(Data!$G$5:$T$144, I$6=Data!$G$4:Data!$T$4),,-1),10)
So basically filter the whole table (g5:t144) based on the column header = this column header, and then sort it and then finally take the first 10 values.
For the other column I would do something similar:
=TAKE(SORT(Data!$B$5:$T$144, XMATCH(C$6,Data!$B$4:Data!$T$4),-1),10,1)
in this case sort the whole tables of data based on the column that matches the header and then take the 10 rows and only the 1st column- Thank you mtarler, So am I plugging the first TAKE formula into the field where the first dollar amount currently shows (cell I7) and the second TAKE into the field where Amazon shows (cell C7)? Can you let me know?
Thank you again- Actually, I plugged them in and they work great! I am very impressed. I've been using Excel since 1991 and I've never heard of TAKE before. How do I mark this as solved?
