# Compare strings for match in separate worksheet and return nearest future date

1. ## Compare strings for match in separate worksheet and return nearest future date

Hello all, this is my first post so I'm looking forward to being a part of such a strong community.

Here's my question:

I have one worksheet with a list of unique line items as indicated by a unique string. I am trying to make one of these columns reflect the closest date in the future corresponding to each item in a separate worksheet. The second sheet has many of the items listed multiple times with different dates. I would like a function that can compare the unique ID in the first column of the first sheet to the range of IDs in the second sheet, then select the nearest future date from the dates corresponding to that ID in the date column and return that.

I have attached a workbook with a mockup of the data. In reference to this document, I need a way to get the nearest future date from column E in the first worksheet to populate the "Date of next closest Task" column in the second worksheet corresponding to the ID of the date.

Ex Wksht Next Date.xlsx

I have a tried a MIN(IF()) function where MIN(IF(AND("Unique ID" = "ID", "Unique Date" >= TODAY()),"Unique Date"), "") but can not get it to work.

If any other information would help, please let me know. Thank you!

2. ## Re: Compare strings for match in separate worksheet and return nearest future date

im not sure if i understood fully but copy this formula into C2 and drag down
=IF(ISERROR(VLOOKUP(A2, 'IDs & Dates'!\$B\$5:\$E\$105, 4, 0)), " ", VLOOKUP(A2, 'IDs & Dates'!\$B\$5:\$E\$105, 4, 0))

oo in the case of duplicates u want the next closest date....i see. i dunno if i know how to do that but ill try

3. ## Re: Compare strings for match in separate worksheet and return nearest future date

kungfood,

I'm fairly certain I have this working as you want it, but please check the results. Formula for what you want is:

``Please Login or Register  to view this content.``
entered AS AN ARRAY FORMULA, using CTRL+SHIFT+ENTER.

4. ## Re: Compare strings for match in separate worksheet and return nearest future date

Brilliant! Thanks guys!

BB1972, that Index did the trick exactly as needed, thank you.

ajm123, much thanks for the iserror idea, I think the only holdup was the limitation of the vlookup.

Thanks again!

5. ## Re: Compare strings for match in separate worksheet and return nearest future date

Glad to help, don't forget you can throw an IFERROR in at the start of that formula, and show nothing/a specific message if there are no future dates for the ID in question.

And thanks for the rep

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