Closest Date with Criteria (with MAXIFS formula?)

My proposal:

Formula:

Please Login or Register  to view this content.

I upgraded to Excel 2021 tonight and am now able to apply all the formulas submitted.

Hans & DJunqueira, the formulas you provided (in Replay #2 & Reply #5) appear to be working but once the date in column A is past the last date in Column G a "no date" value is provided (for example change A6 cell to 12/31/23 and the result is "no date"). Is there a way to tweak the formula so that even if the date in cell A6 is after 12/30/23 it would still display 12/30/23?

I came with that:

Formula:

Please Login or Register  to view this content.

Thank you so much DJunqueira for digging deeper into this formula. Testing it further when i change A2 value to 8/11/23, it's displaying 5/4/23 even though 8/11/23 is one of the dates that MULN had a reverse split.

For the following tests these would be the desired results...
If Cell A2 = 8/10/23, desired result would be 8/11/23.
If Cell A2 = 8/11/23, desired result would be 8/11/23.
If Cell A2 = 8/12/23, desired result would be 8/11/23.

Amending DJunqueira's formula in post #2:
=XLOOKUP(A2,($H$2:$H$6=B2)*($G$2:$G$6),$G$2:$G$6,"No date",IF(A2>MAX($G$2:$G$6),-1,1),1)

I'm very appreciative to all your replies. We're close but it seems every formula provided isn't providing the desired result when Cell A2 = 8/12/23.
If Cell A2 = 8/12/23, desired result would be 8/11/23...

as 8/11/23 is a much closer date than 12/21/23.

I did a wrong move in my second answer...

Formula:

Please Login or Register  to view this content.

Try, =IFERROR(XLOOKUP(A2,($H$2:$H$7=B2)*($G$2:$G$7),($G$2:$G$7)/($H$2:$H$7=B2),"No date",-1,IF(A2>MAX($G$2:$G$7),-1,1)),"No date")

Thanks DJunqueira it still appears that when Cell A2 = 8/12/23 the result in Cell C2 is 12/21/23. The desired result would be 8/11/23 as this would be the closest date.

What i ultimately do is track the biggest gainers in the market each day. Often these big movers can surround recent reverse stock splits. More often than not a stock will run either the very day of the split or a few days after it. But there has also been periods when a stock runs just off the announcement that a reverse split is upcoming... so it might run big 2-3 days before the split even occurs. In either case, if stock ABC runs big and there has been a recent reverse split surrounding the move, i want to track that on my excel spreadsheet. That is why i want it to display the closest day of the split.

I do see the scenario where Column A is exactly in the middle between 2 reverse split dates. In that case i don't really care which date it selects to be closer, as it will be so far out from either reverse split date anyways... in practice these reverse splits for a stock will only happen 2-3 times a year. Really anything past 10 days out either direction the data becomes less meaningful for my purposes. I hope this at least helped explain more what i'm trying to do with this data.

I deal with stock too, I understand pretty well what you want.

I updated my profile version earlier tonight to show "Excel 2021". Hopefully it showing up correctly now.

Hans, your formula in Reply#22 appears to be working perfectly.

=LET(f,FILTER(F$2:F$6,G$2:G$6=B2),IFERROR(XLOOKUP(TRUE,ABS(A2-f)=MIN(ABS(A2-f)),f),"No date"))

Thank you for your persistence and providing that awesome formula!

Hans, would it be possible to use a formula that would lead to the same results but that would be compatible with Excel 2016? Unfortunately some of the users I'm collaborating with are not on the most recent version of Excel (where the filter command is not available).

I'm really in a bind to get this spreadsheet working again. Is there any way to reformat the equation to be compatible with earlier versions of Excel?

This is the equation that works, trying to create the same results without using XLOOKUP or FILTER or MAXIFS:

=LET(f,FILTER(F$2:F$6,G$2:G$6=B2),IFERROR(XLOOKUP(TRUE,ABS(A2-f)=MIN(ABS(A2-f)),f),"No date"))

Lets see if it does the job for you.

Formula:

Please Login or Register  to view this content.

Thank you DJunqueira. That equation is working but for whatever reason the equation is causing the spreadsheet to bog down a lot. The equation is giving the desired results but it's much more sluggish now.

The MAXIF equation you provided previously in this thread also works (and doesn't cause any sluggishness), but for whatever reason when i open the spreadsheet it randomly decides not to function... half the time i get "_xlfn.MAXIFS" to display in the formula, indicating my version of excel isn't registering the formula. I'm so confused i can't even really explain what's happening.

My version of excel is Version 2108 (Build 14332.20637). I have no idea why my MAXIFS function is randomly working on some days and doesn't work on others.

MAXIF is an Excel 2019 function, it should work fine.

I really have no idea why MAXIFS isn't always working with my latest version of Excel 2021. It's even more frustrating that it works certain days and decides to not work the next day. This is becoming difficult to troubleshoot.

You may have a corrupted file, you could try to pass the data to another new file, but with care because you may transfer the corrupted part too. I just deal with a file corrupted in another tread, it was difficult to select columns in a named table, I further investigate and M$ pointed to this possibility. Interestingly it got smaller as I finished the transfer.

Thank you for your responses DJunqueira.

When trying to transfer the data to a new fresh spreadsheet, the xlfn.MAXIFS is showing up, but admittedly i wasn't very careful with the transfer yet (was just a bulk transfer of data.. i'm going to try to recreate the spreadsheet from scratch now).

One interesting thing i've noticed is if i open a fresh blank spreadsheet and then open the spreadsheet of interest, the MAXIFS formula is working properly. So i guess one workaround is opening a blank spreadsheet first before opening my main spreadsheet. Any ideas what might cause that? It seems like i did stumble upon a workaround if nothing else.

I see it as a sign of corrupted file, transfer you data before it is too late and do a backup.

Your file worked as expected with me.
Can't say exactly what is going on with your files, maybe Office installation or problem with HD, you will need to test and try to talk with Microsoft.

I'm positive I'm using Excel 2021... Version 2108 (Build 14332.20637).

I found that the MAXIFS function will work properly in the spreadsheet if I first open the base excel program. The problem of MAXIFS not working arises when I double click my spreadsheet directly without first opening up the base Excel program.

I created a very simple spreadsheet from scratch with a simple MAXIFS formula. The same behavior is occurring (MAXIFS function not working upon reopening the spreadsheet directly... MAXIFS will work if i open the base program first before opening).