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]

Try this formula:

=LET(LFC,A2,LFD,B2,fTab,Sheet2!A1:C20,RESULT,FILTER(INDEX(fTab,0,3),(INDEX(fTab,0,1)=LFC)*(INDEX(fTab,0,2)<=LFD)),INDEX(RESULT,COUNTA(RESULT)))

In this formula the Bold parts have the following meaning:
A2, the cell with the activation code to search
B2, the cell with the searched date
Sheet2!A1:C20, the "Table 2" cells address

Adapt to your situation

 

[BrettOlbrys1]

Try this formula:

=LET(LFC,A2,LFD,B2,fTab,Sheet2!A1:C20,RESULT,FILTER(INDEX(fTab,0,3),(INDEX(fTab,0,1)=LFC)*(INDEX(fTab,0,2)<=LFD)),INDEX(RESULT,COUNTA(RESULT)))

In this formula the Bold parts have the following meaning:
A2, the cell with the activation code to search
B2, the cell with the searched date
Sheet2!A1:C20, the "Table 2" cells address

Adapt to your situation

Thanks, quick question. Will this work in a Table or does it need to be a range?

Try this formula:

=LET(LFC,A2,LFD,B2,fTab,Sheet2!A1:C20,RESULT,FILTER(INDEX(fTab,0,3),(INDEX(fTab,0,1)=LFC)*(INDEX(fTab,0,2)<=LFD)),INDEX(RESULT,COUNTA(RESULT)))

In this formula the Bold parts have the following meaning:
A2, the cell with the activation code to search
B2, the cell with the searched date
Sheet2!A1:C20, the "Table 2" cells address

Adapt to your situation

Anthony, thanks. It appears this does not work within a Table, only a range, because it froze my sheet when I tried to run it in my Table. Is there a formula that works in a Table so any new results will expand automatically with new data? The formula works as expected for a range.

 

[Anthony47]

If Table2 is a structured table then the formula set in the same sheet is table 1 could be:

=LET(LFC,A2,LFD,B2,RESULT,FILTER(Table2[Value],(Table2[Activation Code]=LFC)*(Table2[Date]<=LFD)),INDEX(RESULT,COUNTA(RESULT)))

 

[Peter_SSs]

Assuming both are formal tables, see if this is what you want.

BrettOlbrys1.xlsm
	A	B	C
1	Activation Code	Date When Code Was Used	Value at Date
2	12345	17/02/2022	Northeast
3	12345	4/04/2022	Southeast
4	12345	11/08/2022	Central
5
6
7
8	Activation Code	Date	Value
9	12345	1/01/2022	Northeast
10	12345	1/03/2022	Southeast
11	12345	1/06/2022	Central
12	12345	1/11/2022	West
13
14
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=INDEX(SORT(FILTER(Table2,(Table2[Activation Code]=[@[Activation Code]])*(Table2[Date]<=[@[Date When Code Was Used]]),""),2,-1),1,3)

 

[BrettOlbrys1]

Assuming both are formal tables, see if this is what you want.

BrettOlbrys1.xlsm
	A	B	C
1	Activation Code	Date When Code Was Used	Value at Date
2	12345	17/02/2022	Northeast
3	12345	4/04/2022	Southeast
4	12345	11/08/2022	Central
5
6
7
8	Activation Code	Date	Value
9	12345	1/01/2022	Northeast
10	12345	1/03/2022	Southeast
11	12345	1/06/2022	Central
12	12345	1/11/2022	West
13
14
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=INDEX(SORT(FILTER(Table2,(Table2[Activation Code]=[@[Activation Code]])*(Table2[Date]<=[@[Date When Code Was Used]]),""),2,-1),1,3)

Peter, thanks for the formula. My issue with the formula is that when it calculates, it freezes the program because the calculation time is significant. There are currently 120,000 rows of data, and the CPU usage goes to 100%, so how can I do this without it freezing, do you have any suggestions?

 

[Peter_SSs]

I'm not sure that I will have a better way but to consider that ..

1. Are either, or both, of the tables sorted by Activation Code and then Date?
2. If not, could they be sorted that way?
3. Would a vba solution be acceptable?

 

[BrettOlbrys1]

The tables are sorted by the Activation Date only currently, from oldest to newest. But, if you are asking me if they can be sorted based on Activation Code first (smallest to largest), and then by Activation Date (oldest to newest), then YES, I can sort them that way.

I am open to ANY solution that speeds up the calculation because once new data is entered into the tables, it basically freezes my sheet because it has to recalculate everything.

And I have both tables currently setup as ranges.

 

[SpillerBD]

Peter, thanks for the formula. My issue with the formula is that when it calculates, it freezes the program because the calculation time is significant. There are currently 120,000 rows of data, and the CPU usage goes to 100%, so how can I do this without it freezing, do you have any suggestions?

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.

 

[Alex Blakenburg]

Deleted results didn't look right