[Solved] Excel Problem: Index Match ID and Closest Matching Date

Vishnu

New member
Hi Exceldemy team,
I thank you in advance that your website exist.
I have attached an example of my problem that have been struggling for several days to solve.
Just need to find the respective <Price> from another Table2, matching the YarnID AND the closest <Created Date>
Please help
 

Attachments

  • Excel Problem 1.xlsx
    13.5 KB · Views: 5
Hi Exceldemy team,
I thank you in advance that your website exist.
I have attached an example of my problem that have been struggling for several days to solve.
Just need to find the respective <Price> from another Table2, matching the YarnID AND the closest <Created Date>
Please help
Dear Vishnu,

Welcome to our ExcelDemy Forum! I understand you wish to find the respective <Price> from another Table2, matching the YarnID AND the closest <Created Date>. Fortunately, you can do so by merging VLOOKUP and CHOOSE functions. Here is the combined formula:

=IFERROR(VLOOKUP([@CreatedDate]&[@Yarn1],CHOOSE({1,2},Table2[Created Date]&Table2[Yarn],Table2[Price]),2,0),"")

Note: The IFERROR ensures returning blank cells if the lookup values are not there in the lookup table.

Go to https://www.exceldemy.com/excel-vlookup-with-multiple-criteria/ for more suitable methods. Also, I have attached the Excel file for a better understanding.
 

Attachments

  • Vishnu.xlsx
    14.9 KB · Views: 2
Hi Yousuf,
Thanks for your support.
(Never used this combination and I managed to understand the formula... Thanks to you.)
However, this not working unless Table2 <CreatedDate> match EXACTLY to Table1 <CreatedDate>, which, here is not the case.
The fact is that the date in Table1<CreatedDate> do not always appear in Table2 <CreatedDate>.

What needed next is to get matching closest date in Table2 <CreatedDate> to Table1 <CreatedDate>
Ideally, I would like to see both options; that is <CreatedDate> in Table2 Before & After <CreatedDate> in Table1
Await your response.
Thanks Again for coming this close.
Vishnu
 
Last edited:
Hi Yousuf,
Thanks for your support.
(Never used this combination and I managed to understand the formula... Thanks to you.)
However, this not working unless Table2 <CreatedDate> match EXACTLY to Table1 <CreatedDate>, which, here is not the case.
The fact is that the date in Table1<CreatedDate> do not always appear in Table2 <CreatedDate>.

What needed next is to get matching closest date in Table2 <CreatedDate> to Table1 <CreatedDate>
Await your response.
Thanks Again for coming this close.
Vishnu
Hello Vishnu,

I have reviewed your initial post and it appears that I may have misunderstood it to some extent. Thank you for the detailed explanation, which cleared things up for me.

Regarding your query to find the closest matching date in Table2 <CreatedDate> to Table1 <CreatedDate> and obtain the corresponding price, you can modify the match_type in the previous formula. However, I cannot guarantee its accuracy.

Therefore, the revised formula would be as follows:
=IFERROR(VLOOKUP([@CreatedDate]&[@Yarn1],CHOOSE({1,2},Table2[Created Date]&Table2[Yarn],Table2[Price]),2,TRUE),"")

Please try this formula and let me know if it works properly. If it does not work as intended, we can explore alternative approaches collaboratively.

Regards,
Yousuf Shovon
 
Hi Yousuf,
Unfortunately, it doesn't give the correct value.
At least, it's not showing <N/A> everywhere..
I believe we should keep the exact match because for those values where we have the exact <CreatedDate> & <Yarn>, it actually gives the exact <Price>.
I believe we are close, please hold on...

I'm trying to add a column in Table1, to get the closest date to Table 2 as close as possible matching to respective <Yarn>
Thanks
 
Last edited:
Dear Vishnu,

Did you consider using VBA for this query? I think I have solved this through VBA. I have created a VBA function and have been able to extract exact prices with respect to their closest dates. Before using the VBA code, you need to add a reference to the Microsoft ActiveX Data Objects library in your VBA project. To do this, follow these steps:
  • Open the VBA editor by pressing Alt + F11.
  • From the menu bar, click on Tools and then References.
  • In the References dialog box, scroll down to find Microsoft ActiveX Data Objects x.x Library (where x.x is the version number) and check the box next to it.
  • Click OK to close the dialog box and save the changes.
After adding the reference, the ADODB.Connection type should be recognized by the compiler and the error should be resolved.

The VBA code:
Code:
Option Explicit

Public Function getPrice(ByVal dte As Date) As Currency
    Dim DBFullName As String
    Dim Cnct As String, strsQL As String
    Dim Cn As ADODB.Connection
    Dim Rs As ADODB.Recordset
   
    DBFullName = ThisWorkbook.Path & "\" & ThisWorkbook.Name
   
    Set Cn = New ADODB.Connection
    Cnct = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & DBFullName & "';Extended Properties='Excel 12.0;HDR=Yes;IMEX=1';"
    Cn.Open ConnectionString:=Cnct
   
    Set Rs = New ADODB.Recordset
   
    strsQL = "SELECT TOP 1 T1.Price " _
    & "FROM [Sheet2$] AS T1 " _
    & "WHERE (((T1.[Created Date]) <= #" & Format$(dte, "mm/dd/yyyy") & "#)) ORDER BY T1.[Created Date] DESC;"
   
    With Rs
        .Open strsQL, Cn, adOpenDynamic, adLockReadOnly
        If Not (.BOF And .EOF) Then
            .MoveFirst
            getPrice = .Fields(0)
        End If
    .Close
    End With
   
    Set Rs = Nothing
    Cn.Close
    Set Cn = Nothing

End Function


Private Sub t()
Dim price As Currency
price = getPrice(#11/16/2022#)
End Sub
Try this code and modify it if necessary. I have attached the Excel file for a better understanding. Good luck!

Best Regards.
 

Attachments

  • Vishnu.xlsm
    23.1 KB · Views: 0

Online statistics

Members online
0
Guests online
6
Total visitors
6

Forum statistics

Threads
287
Messages
1,240
Members
508
Latest member
HaroldDyeme
Top