XLOOKUP with Multiple Criteria?

[BrettOlbrys1]

Hello. I have two tables. The first table has an "Activation Code" and the "Date" the Activation code was used. The second table is a historical table of data based on that Activation Code.

Table 1

	A	B
1	Activation Code	Date When Code Was Used
2	12345	2/17/22
3	12345	4/4/22
4	12345	8/11/22

Table 2 - Historical Data

	A	B	C
1	Activation Code	Date	Value
2	12345	1/1/22	Northeast
3	12345	3/1/22	Southeast
4	12345	6/1/22	Central
5	12345	11/1/22	West

What I need to do is look up an Activation Code's value from Table 1 based on the date it was used, against the Table 2 historical data, and return whatever the Activation Code's value was at that time.

So the result, when I look up the codes in Table 1, should be:

12345 on 2/17/22 = Northeast
12345 on 4/4/22 = Southeast
12345 on 8/11/22 = Central

How should the formula be constructed?

 

[Anthony47]

Both formulas of post #4 and #5 will calculate faster if you don't refer to the table as a NamedRange but as Range
So for example,
-if Table2 (Activation Code / Date / Value) are in B1:D100000
-the ActivationCode to be cheched is in I2 and downward and DateWhenCodeWasUsed is in J2 and downward
-then in K2 you might use

=LET(LFC,I2,LFD,J2,RESULT,FILTER($D$2:$D$150000,($B$2:$B$150000=LFC)*($C$2:$C$150000<=LFD)),INDEX(RESULT,COUNTA(RESULT)))

Copy then K2 downward
This will be faster when compared to using Table2[Value] and the other "table centred" names

If that result is still unacceptable, you might try the following macro:

Sub ActivationSearcher()
Dim wOne, wTwo, oArr(), wDates(), wVal()
Dim L1Start As Range, L2Start As Range, dPos As Range
Dim myDic As Object, myK As String, Result As String
Dim I As Long, K As Long, mySplit, mySplit2, myMatch
'
Set L1Start = Sheets("Sheet2").Range("B2")      '<<< 1) The starting corner of the database
Set L2Start = Sheets("Sheet2").Range("I2")      '<<< 2) The starting point of the query param
Set dPos = Sheets("Sheet2").Range("L2")         '<<< 3) The starting point for the results
'
'myTim = Timer
wOne = Range(L1Start, L1Start.End(xlDown)).Resize(, 3).Value
Set myDic = CreateObject("Scripting.Dictionary")
'Store Dates & Values in the Dictionary:
For I = 1 To UBound(wOne)
    myK = wOne(I, 1)
    If myDic.Exists(myK) Then
        myDic.Item(myK) = myDic.Item(myK) & "," & CLng(wOne(I, 2)) & ";" & wOne(I, 3)
    Else
        myDic.Add (myK), CLng(wOne(I, 2)) & ";" & wOne(I, 3)
    End If
'If I = 100000 Then Debug.Print Timer - myTim: Stop
Next I
'Beep
'Second phase:
wTwo = Range(L2Start, L2Start.End(xlDown)).Resize(, 2).Value        'ActivationCode /Date
ReDim oArr(1 To UBound(wTwo), 1 To 1)
For K = 1 To UBound(wTwo)
    myK = wTwo(K, 1)
    If myDic.Exists(myK) Then
        mySplit = Split("0;0," & myDic.Item(myK), ",", , vbTextCompare)
        ReDim wDates(0 To UBound(mySplit))
        ReDim wVal(0 To UBound(mySplit))
        For J = 0 To UBound(mySplit)
            mySplit2 = Split(mySplit(J), ";", , vbTextCompare)
            wDates(J) = CLng(mySplit2(0))
            wVal(J) = mySplit2(1)
        Next J
        myMatch = Application.Match(CLng(wTwo(K, 2)), wDates)
        oArr(K, 1) = wVal(myMatch - 1)
    End If
Next K
dPos.Resize(UBound(oArr), 1).Value = oArr
End Sub

There are three parametres to set; see the lines marked <<<
<<< 1) The starting corner of the database ; this corresponds to B2 in the example I used for the formulas
<<< 2) The starting point of the query param ; this points to the first ActivationCode to search, and the Date is assumed to be the column next to it
<<< 3) The starting point for the results ; typically this is some column at the right of parametre <<< 2

For each ActivationCode + Date set in the query table (as defined by <<<2) it will search into the Database (as set by <<<1) and write in the output position (as set by <<<3) the corresponding Value

This will take manymany seconds to complete (depends on how many unique ActivationCode you have in the DataBase: the less they are repeated, the better) but only when you run it

Finally, you should consider using PowwerQuery, as suggested by SpillerBD (see above)

 

[Peter_SSs]

Hi Alex
I may be completely wrong and I haven't tested with such but I think that with 120,000 rows the strings may become quite long and the manipulations relatively slow.
Anyway, the OP has some large realistic data so it will be easy to tell either way I suspect, but this is my approach. There is a lot of looping but it is in memory and it is in ever-decreasing sized loops assuming the following is true.

The tables are sorted by the Activation Date ..

I have also now taken account of the following and removed the formal table structure.

I have both tables currently setup as ranges.

It may still well be that Power Query is the way to go, but if vba is preferred this is something to test at least.

Original data/layout

BrettOlbrys1.xlsm
	A	B	C	D	E	F	G	H
1	Activation Code	Date When Code Was Used				Activation Code	Date	Value
2	22222	15/05/2021				12345	1/01/2022	Northeast
3	22222	18/01/2022				22222	1/01/2022	a
4	12345	17/02/2022				12345	1/03/2022	Southeast
5	12345	4/04/2022				22222	15/04/2022	b
6	22222	15/04/2022				12345	1/06/2022	Central
7	12345	15/04/2022				22222	25/09/2022	c
8	12345	11/08/2022				12345	1/11/2022	West
9	12345	16/09/2022
10	22222	25/11/2022
Test

Sub Activation_Value()
  Dim d As Object
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, StartRow As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  b = Range("F1").CurrentRegion.Resize(, 3).Value
  With Range("A1").CurrentRegion.Resize(, 3)
    a = .Value
    a(1, 3) = "Value"
    For i = UBound(a) To 2 Step -1
        If Not d.Exists(a(i, 1)) Then d(a(i, 1)) = UBound(b)
        j = d(a(i, 1))
        Do Until (b(j, 2) <= a(i, 2) And b(j, 1) = a(i, 1)) Or j = 1
          j = j - 1
        Loop
        If j > 1 Then
          a(i, 3) = b(j, 3)
          d(a(i, 1)) = j
        Else
          a(i, 3) = "N/A"
        End If
    Next i
    .Value = a
  End With
End Sub

Results

BrettOlbrys1.xlsm
	A	B	C	D	E	F	G	H
1	Activation Code	Date When Code Was Used	Value			Activation Code	Date	Value
2	22222	15/05/2021	N/A			12345	1/01/2022	Northeast
3	22222	18/01/2022	a			22222	1/01/2022	a
4	12345	17/02/2022	Northeast			12345	1/03/2022	Southeast
5	12345	4/04/2022	Southeast			22222	15/04/2022	b
6	22222	15/04/2022	b			12345	1/06/2022	Central
7	12345	15/04/2022	Southeast			22222	25/09/2022	c
8	12345	11/08/2022	Central			12345	1/11/2022	West
9	12345	16/09/2022	Central
10	22222	25/11/2022	c
Test

 

[BrettOlbrys1]

Using PowerQuery to "Merge" your matching results will be tonnes faster. The in-sheet methods get teadious over about 60K+. I've endured 500k lookup tables which helped push into PowerQuery, which is now known as the Get Data.
The PQ results can come out as a new Table, so you will have to adjust to that.

I will try this out and let you know what happens.