Hi All,
I have a tricky INDEX/MATCH function that has me stumped. I attached a workbook that explains what I'm trying to do. If you can think of a solution other than INDEX/MATCH I'm all ears!
Thanks for the help.
Hi All,
I have a tricky INDEX/MATCH function that has me stumped. I attached a workbook that explains what I'm trying to do. If you can think of a solution other than INDEX/MATCH I'm all ears!
Thanks for the help.
I would change your MAX formula in H20 so that it will also work in January, i.e. to this
=MAX(IF(D13:D33=TEXT(TODAY()-DAY(TODAY()),"myyyy"),E13:E33))
and then for the corresponding date you could use this formula:
=INDEX(C13:C33,MATCH(1,(E13:E33=H20)*(D13:D33=TEXT(TODAY()-DAY(TODAY()),"myyyy")),0))
both formulas need to be confirmed with CTRL+SHIFT+ENTER
Audere est facere
Thanks @daddylonglegs, it seems to do what I was trying to do.
One question: What does modifying the 'Month Code' toaccomplish thatFormula:Please Login or Register to view this content.does not?Formula:Please Login or Register to view this content.
Your version works fine for the given data but I was assuming you might want to do the same thing in January 2014 for the previous month's data, i.e. 122013 so your version wouldn't work in that instance
Okay I understand... I was actually trying to find an easy way to do that a while ago and just resorted to a more complex formula, so thanks! Do you have a similar formula for determining last week?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks