Search date + X days prior with Array Formula

**whoisdane** · 07-06-2022, 05:56 PM

Hello!

I have an array function that I use for looking up names automatically within a range and only displaying the names that follow that criteria (shown below)

Please Login or Register  to view this content.

If anything in the status column matched with "funded" it would auto pull all the names and show them in a list. I have the array going from a1 to d250, so if only 25 names are available, it'll only show the 25 and the rest of the cells are blank.

What I am trying to accomplish is instead of the status column showing "funded", it's searching for dates within a certain range of today, and then displaying the names associated with that date. I'm looking for any date that is 365 days prior to today, it will return the name value. I've tried today()-365 and today()<365, but nothing is being pulled in the array.

Thank you for your help! I have uploaded the file for your review.

**Kaper** · 07-07-2022, 03:36 AM

Try it that way (change semicolons to commas or copy a formula from attachment):

Formula:

Please Login or Register  to view this content.

Note that I deleted sample data rows to effectively analyze formula with Formulas-> Formula Auditing -> Evaluate Formula

**Kaper** · 07-07-2022, 03:40 AM

PS. I answered keeping in mind the excel version (2016) you mentioned in your profile.
If you upgraded since you filled in this info in profile, note that with Microsoft 365 it can be done with simple formula which spliis itself down, so can be written just in one cell:

Formula:

Please Login or Register  to view this content.

PS2. I can see we have played together with dates in one of your previous threads: https://www.excelforum.com/excel-for...-and-year.html

**whoisdane** · 07-07-2022, 12:15 PM

Dear Kaper,

You are a god. Thank you.

That worked perfectly!

Search date + X days prior with Array Formula

LinkBack

Thread Tools

Rate This Thread

Display

Search date + X days prior with Array Formula

Re: Search date + X days prior with Array Formula

Re: Search date + X days prior with Array Formula

Re: Search date + X days prior with Array Formula

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Need to calculate when a date is = or less than 30 days prior to today's date

IF statement for two conditions of dates - prior date and then prior three days

[SOLVED] Lookup Date 14 days prior and add multiple values for that date into column

sending an email 7 days prior to a due date

Excel 2007 : i'm having trouble trying to get an alarm to show 30 days prior to the date.

Set alert 30 days prior to date

Excel 03 highlight Vehicle Tax Date 30 days prior to date it runs out

Bookmarks

Bookmarks

Posting Permissions