XLOOKUP with multiple criteria and with month and year

[fayez_MrExcel]

How to "XLookup" the cost center of Emp 123 from the source data with the same month and year.

Destination

Date	Emp No.	Cost Center
Jan 02, 2020	123	Expected Result. --> cc55
Apr 25, 2020	123	Expected Result --> cc90
March 5, 2021	718

Source Data:

Date	Emp No.	Cost Center
Jan 31, 2020	123	cc55
Apr 4, 2020	123	cc90
Aug 2, 2020	718	cc35

 

[fayez_MrExcel]

How to "XLookup" the cost center of Emp 123 from the source data with the same month and year.

Destination

Date	Emp No.	Cost Center
Jan 02, 2020	123	Expected Result. --> cc55
Apr 25, 2020	123	Expected Result --> cc90
March 5, 2021	718

Source Data:

Date	Emp No.	Cost Center
Jan 31, 2020	123	cc55
Apr 4, 2020	123	cc90
Aug 2, 2020	718	cc35

Index match or Vlookup will do. thanks

 

[RoryA]

You could use something like:

=LOOKUP(2,1/(TEXT('Source data'!$A$2:$A$100,"mmyyyy")=TEXT(A2,"mmyyyy"))/('Source data'!$B$2:$B$100=B2),'Source data'!$C$2:$C$100)

 

[fayez_MrExcel]

You could use something like:

=LOOKUP(2,1/(TEXT('Source data'!$A$2:$A$100,"mmyyyy")=TEXT(A2,"mmyyyy"))/('Source data'!$B$2:$B$100=B2),'Source data'!$C$2:$C$100)

That works perfect. Thanks