Xlookup with Date

[kumara_faith]

Hi,

I have the following table:

Book1
	B	C	D	E	F	G	H	I	J	K
2	Table 1				Table 2
3	Name	Date	Branch		Name	Branch1	Transfer Date	Branch2	Transfer Date	Branch3
4	Jonas	1/10/2023	Chicago		Jonas	Chicago
5	Danny	1/10/2023	Dallas		Danny	Dallas
6	Michelle	1/10/2023	New York		Michelle	New York	4/10/2023	Baltimore	1/11/2023	Los Angeles
7	Jonas	1/10/2023	Chicago
8	Danny	5/10/2023	Dallas
9	Michelle	4/10/2023	Baltimore
10	Jonas	1/10/2023	Chicago
11	Danny	1/10/2023	Dallas
12	Michelle	7/10/2023	Baltimore
13	Jonas	4/11/2023	Chicago
14	Danny	4/11/2023	Dallas
15	Michelle	4/11/2023	Los Angeles
Sheet1

In Table 1 I have the name of staffs, the date of the transaction and their branch . In Table 2, I have the name of the staffs and information if there have been transferred and their respective transfer date. Michelle was in New York until she was transferred to Baltimore on 4/10/23 and then again transferred to LA on 1/11/23. As such, the branch name in column D shows the branch according to Table 2. I am trying to use a xlookup to populate the correct branch in column D but I cant seems to include the date logic. Is there any way to get the right results in column D with a formula ? Thanks for all the help.

 

[awoohaw]

Try this;

This requires the time line (with respect to each name) is squential, even though I do not use the date in the formula.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Table 1				Table 2
2	Name	Date	Branch		Name	Branch1	Transfer Date	Branch2	Transfer Date	Branch3
3	Jonas	2023-10-01	Chicago		Jonas	Chicago
4	Danny	2023-10-01	Dallas		Danny	Dallas
5	Michelle	2023-10-01	New York		Michelle	New York	2023-10-04	Baltimore	2023-11-01	Los Angeles
6	Jonas	2023-10-01	Chicago
7	Danny	2023-10-05	Dallas
8	Michelle	2023-10-04	Baltimore
9	Jonas	2023-10-01	Chicago
10	Danny	2023-10-01	Dallas
11	Michelle	2023-10-07	Los Angeles
12	Jonas	2023-11-04	Chicago
13	Danny	2023-11-04	Dallas
14	Michelle	2023-11-04	Los Angeles
15
Sheet2

Cell Formulas
Range	Formula
C3:C14	C3	=INDEX(TOROW(CHOOSECOLS(FILTER($E$3:$J$5,$E$3:$E$5=A3),2,4,6),1),, MIN(COUNTA(TOROW(CHOOSECOLS(FILTER($E$3:$J$5,$E$3:$E$5=A3),2,4,6),1)),COUNTIF($A$3:$A3,A3)))

 

[kumara_faith]

Hi awoohaw,

Thank you for your response. The branch for Michelle for 7/10/2023 should be Baltimore instead of LA. Please assist.

 

[awoohaw]

Okay, try this:

Mr Excel Questions 71.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Table 1				Table 2
2	Name	Date	Branch		Name	Branch1	Transfer Date	Branch2	Transfer Date	Branch3
3	Jonas	2023-10-01	Chicago		Jonas	Chicago
4	Danny	2023-10-01	Dallas		Danny	Dallas
5	Michelle	2023-10-01	New York		Michelle	New York	2023-10-04	Baltimore	2023-11-01	Los Angeles
6	Jonas	2023-10-01	Chicago
7	Danny	2023-10-05	Dallas
8	Michelle	2023-10-04	Baltimore
9	Jonas	2023-10-01	Chicago
10	Danny	2023-10-01	Dallas
11	Michelle	2023-10-07	Baltimore
12	Jonas	2023-11-04	Chicago
13	Danny	2023-11-04	Dallas
14	Michelle	2023-11-04	Los Angeles
15
kumara_faith

Cell Formulas
Range	Formula
C3:C14	C3	=LET(KeyDate, B3, FrstBranch,XLOOKUP(A3,$E$3:$E$5,$F$3:$F$5), MultCity,CHOOSECOLS(FILTER($E$3:$J$5,$E$3:$E$5=A3),4,6), MultDates,CHOOSECOLS(FILTER($E$3:$J$5,$E$3:$E$5=A3),3,5), CityMatch,XLOOKUP(KeyDate,MultDates,MultCity,,-1), IFERROR(CityMatch,FrstBranch))

 

[kumara_faith]

Hi awoohaw,

That you for your response and that worked. Appreciate your patience and have a great day ahead.

 

[awoohaw]

Hi awoohaw,

That you for your response and that worked. Appreciate your patience and have a great day ahead.

I am happy you found a solution here. Welcome to the Mr. Excel forum.

Best Wishes!