Return nearest date after finding match ID

[cloudy123456]

I have 2 sets of data. One contains ID# and dates . The other set contains same ID # and different dates. Each ID # occurs multiple times, but not equal number of times, in each spreadsheet. I want a formula to look for ID # match in the second spreadsheet and return the date that is nearest to the date in spreadsheet formula is in.

 

[WarPigl3t]

Lots of unanswered questions here. Im not going to ask them all because its bed time. I will only ask one.
Main date is 5/15/2019
Lookup is 5/17/2019
2nd lookup is 5/13/2019
How is this handled.

 

[cloudy123456]

Main sheet has inventory ID# and date of shipping. Ancillary sheet has inventory ID# and date rcvd. Should be within a week of each other. How can I get this in one sheet?

 

[WarPigl3t]

Are you willing to incorporate VBA into this?

 

[cloudy123456]

If that’s the only way to solve it. I’m fairly skilled with excel formulas but not so much with vba. Willing to try it tho.

 

[WarPigl3t]

Wait dont use this one. Its not done. I forgot to incorporate the code for the ID must be matching. In that case I cant use a For Each loop and this wont work at all. Sorry but i gotta go to sleep and the loop you need is more complicated.

Lets use a custom function. Works just like a formula but you gotta code it in VBA.

Function myFunction(lookupDate, lookupRange)
   [COLOR=#008000]  'lookupDate = Date of shipping
     'lookupRange = Dates received [/COLOR]
     myVariable = 999999999
     For Each element in lookupRange
          If element < myVariable _
          And element > lookupDate Then
               myVariable = element
          End If
     Next element
     myFunction = myVariable
End Function

=myFunction(B2, Ancillary!$B$2:$B$9999)

 

[WarPigl3t]

Can't sleep when a problem is not solved.

Lets use a custom function. Works just like a formula but you gotta code it in VBA.

Function myFunction(lookupID, lookupDate, lookupRange)
  [COLOR=#008000]   'lookupID = ID of shipping[/COLOR]
    [COLOR=#008000] 'lookupDate = Date of shipping
     'lookupRange = IDs and Dates received, Columns A and B
[/COLOR]     Application.Volatile
     myVariable = 999999999
     myBool = True
     For Each element in lookupRange
          If myBool = True Then
               If lookupID = element Then
                    myBool = False
               End If
          Else
                If element < myVariable _
                And element > lookupDate Then
                     myVariable = element
                End If
                myBool = True
          End If
     Next element
     myFunction = myVariable
End Function

=myFunction(A2, B2, Ancillary!$A$2:$B$9999)

 

[cloudy123456]

Im getting error ...im sure its user error on my part with VBA. Excuse my ignorance on VBA, but do i have to change the code at all? I made sure my column names are the same as stated in the code.