# Extract dates from VLOOKUP

1. ## 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?

2. ## 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

3. ## 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. ## Re: Extract dates from VLOOKUP

Originally Posted by mikey141
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. ## Re: Extract dates from VLOOKUP

Hi, May be this

Formula:
`Please Login or Register  to view this content.`

6. ## 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)))

7. ## 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

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

#### 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