How to Retrieve an ID from One Google Sheets Table Based on Date Comparison with Another Table

Question

I have two tables in Google Sheets and I need to retrieve an ID based on a date column. Table 1 has the monthly sales data while table 2 has the information if a store is open or closed for a given date. If the store has closed then Closing will have data, otherwise is blank (store is not closed).

So basically, both tables have the same structures: Name, ID, and Date columns.

Table 1)

Name	ID	Date Sales
A		1/1/2000
A		1/1/2005
B		1/1/2000
B		1/1/2005
B		1/1/2023
...	...	...
A		1/1/2023

Table 2)

Name	ID	Closing
A	1	1/1/2005
A	2
B	3	1/1/2008
A	4

What I want is that if Date Sales is less than Closing, then the first ID from Table 2 should be retrieved in ID for Table 1.

The result should be something like:

Name	ID	Date Sales
A	1	1/1/2000
A	2	1/1/2005
B	3	1/1/2000
B	3	1/1/2005
B	4	1/1/2023
...	...	...
A	2	1/1/2023

I think this can be done with a combination of VLOOKUP, INDEX, and MATCH. However, my formula so far is not able to retrieve the correct ID for the given date.

How can I accomplish this?

Answer 1

How about using Xlookup function :

=ARRAYFORMULA(XLOOKUP((C2:C25)+1,C$29:C$32,B$29:B$32,,1))

Note: Adjust the range according to your data

C2:C25 range is for the data sales , I add plus 1 so that the date will be included for the previous ID.

C29:C32 range is for the closing dates

B29:B32 is for the IDs

RESULT: