XLOOKUP and LOOKUP Combined

[austinandreikurt]

Hi,
I need help to combine both formula's into one. In simple term, this is a VLOOKUP with 2 criteria but I can't merge cells to simplify it because the second criteria is a date range, thus I need a LOOKUP.
For example, I have this in a table:

Employee ID	Date of Change (CheckDate)	RATE
1XXX001	01-Jan-20​	27.57
1XXX001	12-Mar-21​	28.26
1XXX002	01-Jan-20​	27.57
1XXX002	12-Mar-21​	28.26
1XXX005	01-Jan-20​	27.57
1XXX005	09-Apr-21​	28.26
1XXX005	20-Jun-21​	30.55

Say I have the Employee ID list in Column A then date range in Column B and I need to put the formula to get the rates in Column C.
Then if I am looking for the rate of Employee 1XXX001 (Cell A2) as of 20-Feb-21 (Cell B2), then the formula in Cell C2 should result to 27.57 because the 28.26 is only effective since 12-Mar-21 onwards.
Another sample is for Employee 1XXX005 (Cell A3) with date 30-Jun-21 (Cell B3), rate I need to get in Cell C3 is 30.55.

Employee ID	Date	RATE
1XXX001	20-Feb-21​	27.57
1XXX005	30-Jun-21​	30.55
1XXX002	02-Jan-20​	27.57
1XXX005	09-Apr-21​	28.26

 

[jasonb75]

Don't need to combine both, just use lookup. You wouldn't have XLOOKUP in your 2016 version of excel anyway.

Book1
	A	B	C
1	Employee ID	Date of Change (CheckDate)	RATE
2	1XXX001	01-Jan-20	27.57
3	1XXX001	12-Mar-21	28.26
4	1XXX002	01-Jan-20	27.57
5	1XXX002	12-Mar-21	28.26
6	1XXX005	01-Jan-20	27.57
7	1XXX005	09-Apr-21	28.26
8	1XXX005	20-Jun-21	30.55
9
10	Employee ID	Date	RATE
11	1XXX001	20-Feb-21	27.57
12	1XXX005	30-Jun-21	30.55
13	1XXX002	02-Jan-20	27.57
14	1XXX005	09-Apr-21	28.26
Sheet3

Cell Formulas
Range	Formula
C11:C14	C11	=LOOKUP(2,1/($A$2:$A$8=A11)/($B$2:$B$8<=B11),$C$2:$C$8)

 

[austinandreikurt]

That was quick. Just what I needed. Thanks @jasonb75 ! ?