+ Reply to Thread
Results 1 to 7 of 7

Extract dates from VLOOKUP

  1. #1
    Registered User
    Join Date
    05-14-2015
    Location
    Kent
    MS-Off Ver
    365
    Posts
    20

    Unhappy Extract dates from VLOOKUP

    I have a big database which need to extract the three most dates with 6 months into another table when looking up the same ID. I’ve tried a pivot table but can only get “min” “max” results but really need those three dates within the last six months from today and display in another table.

    Lookup cell A3 in Sheet 1 from sheet 2 A:A for that ID Number and if more than 3 events and “misuse/other” in cell G:G matches then return the most dates within 6 months back into Sheet 1.

    or do I try something completely different?
    Attached Files Attached Files

  2. #2
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,524

    Re: Extract dates from VLOOKUP

    =AGGREGATE(14,6,Sheet2!$B$1:$B$12/((Sheet2!$A$1:$A$12=$A3+0)*(Sheet2!$G$1:$G$12="Misuse/Other")),COLUMNS($H3:H3))
    try this, copy and paste towards right
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  3. #3
    Registered User
    Join Date
    05-14-2015
    Location
    Kent
    MS-Off Ver
    365
    Posts
    20

    Re: Extract dates from VLOOKUP

    Samba,
    Thanks for your reply and see it returns the Small and Large values, but is there a way to return the next date behind the largest value?

  4. #4
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,524

    Re: Extract dates from VLOOKUP

    Quote Originally Posted by mikey141 View Post
    Samba,
    Thanks for your reply and see it returns the Small and Large values, but is there a way to return the next date behind the largest value?
    it did what you asked, where it is giving wrong results

  5. #5
    Forum Expert boopathiraja's Avatar
    Join Date
    07-12-2013
    Location
    Coimbatore,TamilNadu, India
    MS-Off Ver
    Excel 2007, 2013, 2016, 365
    Posts
    1,455

    Re: Extract dates from VLOOKUP

    Hi, May be this

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Click just below left if it helps, Booþathì

  6. #6
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,956

    Re: Extract dates from VLOOKUP

    I guess with 6 months is with in previous 6 months from today.

    C3
    =COUNTIFS(Sheet2!A1:A12,A3,Sheet2!F1:F12,B3,Sheet2!G1:G12,"*"&C1&"*",Sheet2!B1:B12,">="&EDATE(TODAY(),-6))

    D3 spill array
    =INDEX(SORT(FILTER(Sheet2!B1:B12,(Sheet2!A1:A12=--A3)*(Sheet2!F1:F12=B3)*ISNUMBER(SEARCH(C1,Sheet2!G1:G12))*(Sheet2!B1:B12>=EDATE(TODAY(),-6)))),SEQUENCE(,MIN(3,C3)))
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    05-14-2015
    Location
    Kent
    MS-Off Ver
    365
    Posts
    20

    Re: Extract dates from VLOOKUP

    Morning All,
    Thanks for the replies already and very sorry on the confusion and some of these do answer my question, but doesnt give me the ability to return all the dates from today and within 6 months having 3 events under the same ID.

    Its my fault not giving the right information and having dates older than the six months which hasn't helped you guys so I've uploaded a new spreadsheet showing what it should look like.

    Sorry again..
    Mike
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] How do I extract dates from a list
    By mtouhig in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-27-2020, 06:08 AM
  2. Extract the dates missing per item from a list of dates
    By Freelanx in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 07-10-2018, 01:08 AM
  3. [SOLVED] How do I extract data between dates?
    By Budhdr in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 01-26-2018, 09:12 AM
  4. Extract data between two dates
    By Tarentum in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-28-2017, 05:48 PM
  5. Replies: 3
    Last Post: 11-25-2015, 10:58 AM
  6. Extract data between two dates
    By Ogi in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 10-24-2014, 07:19 AM
  7. [SOLVED] Vlookup dates between two dates and count the number of days
    By nishikanth in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-28-2013, 03:25 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1