Using INDEX and Match when the column in the second MATCH argument changes

[Gamer85308]

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

[#2 · Accepted solution]

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

[#3]

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

[#4]

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?

[#5]

you are very welcome, you can make an answer as solved by clicking on the 3dots of the correct post and selecting 'mark this as best solution' or whatever it says. feel free to like the post too 🙂
As for TAKE it is relatively NEW. In fact that are a LOT of new things that have come to Excel including XLOOKUP, XMATCH, FILTER, TAKE, DROP, CHOOSEROWS, .... and I could go on. But 2 of the Biggest game changers I highly recommend learning are LET() and LAMBDA(). Once you start getting into those you will find and use and need a lot of these other new functions and the Lambda helper functions.

[#6]

Thank you, I did like and mark your post as the best response. I've recently discovered Filter and Choosecols. I've used both together and I've also recently used Xmatch. Haven't tried Drop or Chooserows yet. I will look up LET and LAMBDA. Thanks again for your help!