lookup match return closest to x date

288enzo

Well-known Member
Joined
Feb 8, 2009
Messages
717
Office Version
  1. 2016
Platform
  1. Windows
Hi all,

Happy New Year, this is my first post in 2022.

I have two worksheets I'm working with.

worksheet one is titled KPI Data - MY22 ONLY
It is comprised of a list of unique data starting at F3 and a date starting at I3.

The other worksheet (CR 11.20 to 1.9) is comprised of data starting at A2 and a date starting at B2.

What I'm trying to do is match the data in KPI Data - MY22 ONLY with the data in CR 11.20 to 1.9, the kicker is that I'm looking for the date in column B that is closest (+/-) to the date in column I, then return the difference between those two dates in KPI Data - MY22 ONLY Column C.

I sure hope that makes sense :unsure:

I'm only pasting the first 100 lines of data. There is actually 21,182, so you may not find a match.

Book1.xlsx
AB
1DataDate
2WA1EAAFY5N202708911/20/2021
3WAUC4CF51NA00443411/20/2021
4WAUCBCF56NA00888711/20/2021
5WA1EAAFY4N203208711/20/2021
6WA1EECF33N101503311/20/2021
7WA1GAAFY0N203426511/20/2021
8WA1LAAGE4NB00670111/20/2021
9WAU92BF2XNN01431011/20/2021
10WAU72BF20NN01468011/20/2021
11WA1GAAFY0N202910111/20/2021
12WA1AUCF38N103788511/20/2021
13WA1BAAFY2M213820211/20/2021
14WA1EECF38N101661511/20/2021
15WA1LJBF75ND00354511/20/2021
16WAUABCF53NA00554811/20/2021
17WAUW4GF52NN00102311/20/2021
18WA1BNAFY9L203561911/20/2021
19WA1EECF31N101797611/20/2021
20WA1EECF34N103136811/20/2021
21WA12AAGE2NB00923611/20/2021
22WAUBBCF57NA00377411/20/2021
23WAUABCF50NA00471411/20/2021
24WA1DECF39N103313011/20/2021
25WA15AAFY3N200492711/20/2021
26WA1DECF37N101814211/20/2021
27WA1EAAFY8N200572011/20/2021
28WA1VAAF76HD02672711/20/2021
29WA1AAAFY8M213059811/20/2021
30WA1LAAGE7NB00947711/20/2021
31WA124AFY3N202893211/20/2021
32WA1AAAFY7M211688511/20/2021
33WA1LAAGEXNB00907011/20/2021
34WA1EAAFY8N202693811/20/2021
35WAUEJBFW7N700319211/20/2021
36WA1B4AFYXN203904011/20/2021
37WAUH3DGY4NA00511611/20/2021
38WA1EECF32N100791211/20/2021
39WA1AAAGE9NB00555211/20/2021
40WA1EECF39N101914911/20/2021
41WA1C4AFYXM209111111/20/2021
42WA1EECF3XN101872311/20/2021
43WAUCBCF50NA00608311/20/2021
44WA1EAAFY5N201674111/20/2021
45WAUABCF57NA00605511/20/2021
46WA1AAAGE7NB01096111/20/2021
47WAUY4GF57NN00137911/20/2021
48WA1AAAGE0NB01083411/20/2021
49WA1B4AFY6N203126011/20/2021
50WA1EAAFYXN202611611/20/2021
51WA1LAAGE5NB01175911/20/2021
52WA1LJBF71ND00120911/20/2021
53WA1EECF32N100626011/20/2021
54WAUDFAF28MN07653711/20/2021
55WA1C4AFY5N203189811/20/2021
56WAU8EAF87MN00490211/20/2021
57WAUY4GF50NN00131911/20/2021
58WA1VWBF79ND00008911/20/2021
59WUAPCBF25NN90074111/20/2021
60WA1AAAFY2M213787111/20/2021
61WAUH3DGYXNA00400411/20/2021
62WA1EECF38N102972211/20/2021
63WA14AAFY0N200086311/20/2021
64WAUABCF54MA03085911/20/2021
65WA1AJBF72ND00415711/20/2021
66WA1EAAFY4N201640711/20/2021
67WA1AUCF3XN104114511/20/2021
68WA1GAAFY1N203103511/20/2021
69WAU8SAF89MN02745911/20/2021
70WAUV2BF28NN01403111/20/2021
71WA1EECF37N103209311/20/2021
72WAUY4GF5XNN00135811/20/2021
73WUA1CBF22NN90125411/20/2021
74WA1DECF38N103149611/20/2021
75WA1FAAFY1N200413711/20/2021
76WA1CVBF11ND00635911/20/2021
77WA1EECF30N104098711/20/2021
78WA1EECF33N103521711/20/2021
79WA1GAAFYXN203185711/20/2021
80WA1LAAGE5NB00938111/20/2021
81WA1LAAGE9NB00924011/20/2021
82WA1VXBF71ND00369411/20/2021
83WUAKBAFX3N790037311/20/2021
84WA1EECF35N103073211/20/2021
85WA1AUCF35N104140811/20/2021
86WA1GAAFY6N202926411/20/2021
87WAUFJBFW1N700442411/20/2021
88WA1EECF38N103025511/20/2021
89WAUY4GF55NN00116811/20/2021
90WA1B4AFY1N203549111/20/2021
91WA1EECF39N103316411/20/2021
92WA1GAAFY4N202921511/20/2021
93WA12AAGE2MB01509711/20/2021
94WAUBBAF41NN00348311/20/2021
95WA1AUCF3XN103759511/20/2021
96WA1EECF35N103330711/20/2021
97WA1GAAFY5N201623211/20/2021
98WA1LXBF78ND00355411/20/2021
99WAUABCF53NA00562911/20/2021
100WAUCBCF55MA06250411/20/2021
101WAUE3BF22NN01381211/20/2021
CR 11.20 to 1.9


Book1.xlsx
ABC
2DataDateDifference
3WA1EAAFY9N204609612/31/2021
4WA1DECF36N105799712/31/2021
5WA1DECF35N104702512/31/2021
6WAUABAF49NN00460412/31/2021
7WA1CWBF10ND00929612/31/2021
8WA1EABFY5N204723512/31/2021
9WA1FAAFY1N204348612/31/2021
10WAUGUDGY6NA01824712/31/2021
11WAUHUDGY6NA01834812/31/2021
12WAUW4GF55NN00167712/31/2021
13WA1C4AFYXN204151912/31/2021
14WAU72BF24NN01317512/31/2021
15WA1EECF30N104411712/31/2021
16WA1AAAGE9NB01114212/31/2021
17WAUCBAF41NN00242112/31/2021
18WA1DECF37N104423812/31/2021
19WA1A4AFY3N203780512/31/2021
20WAUFACF56NA01347412/31/2021
21WAUDAAF4XNN00418812/31/2021
22WA1AAAGE3NB01345312/31/2021
23WAUEAAF47NN00407212/31/2021
24WA1LAAGE3NB01309012/31/2021
25WA1AAAGE4NB01294312/31/2021
26WA1FVBF16ND00646612/31/2021
27WAUYAGF53NN00172712/31/2021
28WA1EAAFY2N204678112/31/2021
29WA1EECF37N104974912/31/2021
30WA1EECF31N105170712/31/2021
31WA17AAF40NA00660112/31/2021
32WA1LAAGE6NB01144612/31/2021
33WU1ARBF10ND00902912/31/2021
34WA1DECF31N105406712/31/2021
35WA1DECF30N105358712/31/2021
36WA1EAAFY9N204412312/31/2021
37WAUPFBF24NN01856012/31/2021
38WAUAUDGY6NA01814212/31/2021
39WA15AAFY7N204530412/31/2021
40WA1LXBF78ND00563012/31/2021
41WA124AFY5N203893012/31/2021
42WA124AFY7N204241412/31/2021
43WA134AFY7N203783312/31/2021
44WAUEAAF48NN00407812/31/2021
45WAUP4AF59NA01082712/31/2021
46WA1LXBF73ND00584312/31/2021
47WA1FAAFY9N204586012/31/2021
48WAUAHBFW1N790237912/31/2021
49WA1AUCF31N102042612/31/2021
50WA1EAAFYXN204443712/31/2021
51WA1EAAFY5N204511012/31/2021
52WAUV2BF23NN00883312/31/2021
53WAUW4GF59NN00173212/31/2021
54WA1LJBF72ND00434512/31/2021
55WAUABCF55NA01124012/31/2021
56WA1FVBF10ND00654412/31/2021
57WA1LXBF7XND00525612/31/2021
58WA1EECF35N105175712/31/2021
59WAUCBCF5XNA01133812/31/2021
60WA1B4AFY0N203940412/31/2021
61WAUABCF50NA00622212/31/2021
62WA1C4AFY8N204010912/31/2021
63WAUAUDGYXNA03010212/31/2021
64WA12ABGE3NB00848712/31/2021
65WA11AAGE2NB01385012/31/2021
66WA1DECF31N102170212/31/2021
67WA1LJBF7XND00529112/31/2021
68WA1DECF38N102912012/31/2021
69WAU92BF20NN01423512/31/2021
70WAUFJBFW5N700414912/31/2021
71WA1EECF36N105332412/31/2021
72WA1LXBF7XND00580712/31/2021
73WAUEAAF40NN00407412/31/2021
74WA1EECF3XN105122212/31/2021
75WA124AFY4N204264512/31/2021
76WA15AAFY6N204681612/31/2021
77WA1B4AFY3N204490512/31/2021
78WA1BVBF13ND00983912/31/2021
79WA1EECF3XN104512812/31/2021
80WA1EECF39N104503812/31/2021
81WA1VXBF70ND00572712/31/2021
82WAUBBAF45NN00371412/31/2021
83WA1FAAFY6N204300112/31/2021
84WAUFACF51NA01288012/31/2021
85WA1B4AFY1N204054512/31/2021
86WAUCJBFW6N700158512/31/2021
87WA1DECF34N104391912/31/2021
88WA1LJBF78ND00626212/31/2021
89WA1CWBF15ND00835412/31/2021
90WA1EAAFY7N204489512/31/2021
91WAUFJBFWXN700379012/31/2021
92WA1B4AFY8N204041412/31/2021
93WA1VABGE7NB01550512/31/2021
94WA1LXBF77ND00556812/31/2021
95WA1LJBF75ND00542512/31/2021
96WAUCBCF51NA01137312/31/2021
97WA1DECF39N103205712/31/2021
98WA1FAAFYXN204321312/31/2021
99WAUD3BF2XNN01659612/31/2021
100WA1LABGE1NB00725912/31/2021
101WA1EECF3XN104835312/31/2021
102WAUEAAF49NN00255112/31/2021
KPI Data - MY22 ONLY
 
I'll have to manually soft the data and check,
sorry, but what means manually soft the data ?
The 1st column, you added is the absolute days difference
The 2nd column is the corresponding date (set the numberformat to "mm/dd/yy" or similar.
the 3rd column is 3 parts with a pipe as separator, part1 must be equal to the wanted code, part 2 is again that date and part 3 is the rownumber in the other listobject.
Of course, you don't have to copy all 3 columns, that was just for me for checking

As there is only 1 record of each code in the 2nd table, JEC's method is smoother, quicker.
 
Upvote 0

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Try this dictionary method

VBA Code:
Sub jec()
 Dim ar, ar2, a As Variant, i As Long, j As Long
 ar = Sheets("KPI Data - MY22 ONLY").Cells(1, 1).CurrentRegion
 ar2 = Sheets("CR 11.20 to 1.9").Cells(1, 1).CurrentRegion
 
 With CreateObject("scripting.dictionary")
   For i = 2 To UBound(ar)
     For j = 2 To UBound(ar2)
        If Not .exists(ar(i, 1)) And ar(i, 1) = ar2(j, 1) Then
           .Item(ar(i, 1)) = Array(ar(i, 1), Abs(ar(i, 2) - ar2(j, 2)))
        ElseIf ar(i, 1) = ar2(j, 1) Then
            a = .Item(ar(i, 1))
            If Abs(ar(i, 2) - ar2(j, 2)) < a(1) Then a(1) = Abs(ar(i, 2) - ar2(j, 2))
           .Item(ar(i, 1)) = a
        End If
      Next
      If IsEmpty(.Item(ar(i, 1))) Then .Item(ar(i, 1)) = Array(ar(i, 1), "No match")
    Next
  Sheets("KPI Data - MY22 ONLY").Cells(2, 6).Resize(.Count) = Application.Index(.items, 0, 2)
 End With
End Sub
It worked perfectly....it took awhile, thought Excel froze on me. I'll now have to work on learning what it is you did :)
 
Upvote 0
sorry, but what means manually soft the data ?
The 1st column, you added is the absolute days difference
The 2nd column is the corresponding date (set the numberformat to "mm/dd/yy" or similar.
the 3rd column is 3 parts with a pipe as separator, part1 must be equal to the wanted code, part 2 is again that date and part 3 is the rownumber in the other listobject.
Of course, you don't have to copy all 3 columns, that was just for me for checking

As there is only 1 record of each code in the 2nd table, JEC's method is smoother, quicker.
Sorry, I meant I wanted to check if it worked as intended. Thank you for all the work, I'll play around with it to learn. All your comments are very much appreciated to help me better understand the inner workings of your script.
 
Upvote 0
Try this dictionary method

VBA Code:
Sub jec()
 Dim ar, ar2, a As Variant, i As Long, j As Long
 ar = Sheets("KPI Data - MY22 ONLY").Cells(1, 1).CurrentRegion
 ar2 = Sheets("CR 11.20 to 1.9").Cells(1, 1).CurrentRegion
 
 With CreateObject("scripting.dictionary")
   For i = 2 To UBound(ar)
     For j = 2 To UBound(ar2)
        If Not .exists(ar(i, 1)) And ar(i, 1) = ar2(j, 1) Then
           .Item(ar(i, 1)) = Array(ar(i, 1), Abs(ar(i, 2) - ar2(j, 2)))
        ElseIf ar(i, 1) = ar2(j, 1) Then
            a = .Item(ar(i, 1))
            If Abs(ar(i, 2) - ar2(j, 2)) < a(1) Then a(1) = Abs(ar(i, 2) - ar2(j, 2))
           .Item(ar(i, 1)) = a
        End If
      Next
      If IsEmpty(.Item(ar(i, 1))) Then .Item(ar(i, 1)) = Array(ar(i, 1), "No match")
    Next
  Sheets("KPI Data - MY22 ONLY").Cells(2, 6).Resize(.Count) = Application.Index(.items, 0, 2)
 End With
End Sub
I made a few changes, do they look right to you? Also, can I use a wildcard in a Variant?
VBA Code:
Sub jec()
 Dim ar, ar2, a As Variant, i As Long, j As Long
 'ar = Sheets("KPI Data - MY22 ONLY").Cells(1, 1).CurrentRegion
 ar = Sheets("KPI Data*").Cells(1, 7).CurrentRegion 'The data to match is in Column G, added wildcard
 'ar2 = Sheets("CR 11.20 to 1.9").Cells(1, 1).CurrentRegion
 ar2 = Sheets("CR*").Cells(1, 1).CurrentRegion 'Added wildcard
 
 With CreateObject("scripting.dictionary")
   For i = 2 To UBound(ar)
     For j = 2 To UBound(ar2)
        If Not .exists(ar(i, 1)) And ar(i, 1) = ar2(j, 1) Then
           .Item(ar(i, 1)) = Array(ar(i, 1), Abs(ar(i, 2) - ar2(j, 2)))
        ElseIf ar(i, 1) = ar2(j, 1) Then
            a = .Item(ar(i, 1))
            If Abs(ar(i, 2) - ar2(j, 2)) < a(1) Then a(1) = Abs(ar(i, 2) - ar2(j, 2))
           .Item(ar(i, 1)) = a
        End If
      Next
      If IsEmpty(.Item(ar(i, 1))) Then .Item(ar(i, 1)) = Array(ar(i, 1), "No match")
    Next
  'Sheets("KPI Data - MY22 ONLY").Cells(2, 6).Resize(.Count) = Application.Index(.items, 0, 2)
  Sheets("KPI Data - MY22 ONLY").Cells(2, 25).Resize(.Count) = Application.Index(.items, 0, 2) 'I want the results in Column Y
 End With
End Sub
 
Upvote 0
No you can't use wildcards like that, referring to sheets.
The last line looks fine!
 
Upvote 0
You're welcome!
JEC, sorry to bug you again, but I've moved my columns around and am not sure how to adjust your macro.

Sheet KPI Data
I moved the column with the numbers to match from A to G
I moved the column with the dates from B to U

I did change the cell reference from 1 to 7 in line 8, but that was it. I know I need to change the cell reference in the loop, I just don't want to screw it up.

VBA Code:
Sub jec()
 Dim ar, ar2, sq, sp, a As Variant, i As Long, j As Long, sh As Worksheet

 For Each sh In ActiveWorkbook.Sheets
   If sh.Name Like "KPI Data*" Then sq = sh.Name
   If sh.Name Like "CR*" Then sp = sh.Name
 Next
 If sp = "" Or sq = "" Then MsgBox "You need 2 sheets to move on": Exit Sub
 ar = Sheets(sq).Cells(1, 7).CurrentRegion
 ar2 = Sheets(sp).Cells(1, 1).CurrentRegion
  
 With CreateObject("scripting.dictionary")
   For i = 2 To UBound(ar)
     For j = 2 To UBound(ar2)
        If Not .exists(ar(i, 1)) And ar(i, 1) = ar2(j, 1) Then
           .Item(ar(i, 1)) = Array(ar(i, 1), Abs(ar(i, 2) - ar2(j, 2)))
        ElseIf ar(i, 1) = ar2(j, 1) Then
            a = .Item(ar(i, 1))
            If Abs(ar(i, 2) - ar2(j, 2)) < a(1) Then a(1) = Abs(ar(i, 2) - ar2(j, 2))
           .Item(ar(i, 1)) = a
        End If
      Next
      If IsEmpty(.Item(ar(i, 1))) Then .Item(ar(i, 1)) = Array(ar(i, 1), "No match")
    Next
  Sheets("KPI Data - MY22 ONLY").Cells(2, 25).Resize(.Count) = Application.Index(.items, 0, 2) 'I want the results in Column Y
 End With
End Sub
 
Upvote 0
This too much of guessing.
You need to post your complete sample wb before someone can help in this case
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top