Lookup With Gaps

[Steve 1962]

Hi All

Trying to find a lookup formula that lives in columns B & C, that refers to a source table that lives in columns E, F & G.
I want the lookup to return a value from column G that corresponds with the lookup date (column A) & category (columns A or B). However, there are both gaps in the dates of the lookup and source (not sequential).
If the lookup date (column A) is not found in the source date (column E), then return the value from the nearest earlier date associated with column G.
Thanks

Book1
	A	B	C	D	E	F	G
1	Date	A	B		Date	Category	Value
2	3/06/2024				8/06/2024	B	55
3	4/06/2024				10/06/2024	A	60
4	5/06/2024				11/06/2024	B	21
5	6/06/2024				16/06/2024	B	23
6	7/06/2024				16/06/2024	A	11
7	10/06/2024	60	55	For Cat B - Require value from nearest earliest date	18/06/2024	B	53
8	11/06/2024		21		19/06/2024	Z	85
9	12/06/2024				22/06/2024	F	54
10	13/06/2024
11	14/06/2024
12	17/06/2024	11	23	For Cat A - Require value from nearest earliest date
13	18/06/2024		53
14	19/06/2024
Sheet6

 

[Eric W]

Try:

Book1
	A	B	C	D	E	F	G
1	Date	A	B		Date	Category	Value
2	6/3/2024				6/8/2024	B	55
3	6/4/2024				6/10/2024	A	60
4	6/5/2024				6/11/2024	B	21
5	6/6/2024				6/16/2024	B	23
6	6/7/2024				6/16/2024	A	11
7	6/10/2024	60	55	For Cat B - Require value from nearest earliest date	6/18/2024	B	53
8	6/11/2024	60	21		6/19/2024	Z	85
9	6/12/2024	60	21		6/22/2024	F	54
10	6/13/2024	60	21
11	6/14/2024	60	21
12	6/17/2024	11	23	For Cat A - Require value from nearest earliest date
13	6/18/2024	11	53
14	6/19/2024	11	53
15
Sheet10

Cell Formulas
Range	Formula
B2:C14	B2	=IFERROR(VLOOKUP($A$2:$A$14,FILTER($E$2:$G$9,$F$2:$F$9=B1),3),"")
Dynamic array formulas.

 

[Steve 1962]

Thanks Eric W - nearly there.
Just need to have blanks in dates that have past. EG: require the 60 value in B7 but not from there on (require blanks in cells B6 to B11) and do not require value 21 in cells C9 to C11.

 

[Phuoc]

Try this:

Book1
	A	B	C	D	E	F	G
1	Date	A	B		Date	Category	Value
2	3/6/2024				8/6/2024	B	55
3	4/6/2024				10/6/2024	A	60
4	5/6/2024				11/6/2024	B	21
5	6/6/2024				16/6/2024	B	23
6	7/6/2024				16/6/2024	A	11
7	10/6/2024	60	55	For Cat B - Require value from nearest earliest date	18/6/2024	B	53
8	11/6/2024		21		19/6/2024	Z	85
9	12/6/2024				22/6/2024	F	54
10	13/6/2024
11	14/6/2024
12	17/6/2024	11	23	For Cat A - Require value from nearest earliest date
13	18/6/2024		53
14	19/6/2024
Sheet1

Cell Formulas
Range	Formula
B2:C14	B2	=IFERROR(LOOKUP(2,1/($F$2:$F$9=B$1)/($E$2:$E$9<=$A2)/($E$2:$E$9>N($A1)),$G$2:$G$9),"")

 

[Steve 1962]

Plus I'm also getting the SPILL! in the cell.

 

[Steve 1962]

Absolutely perfect - thanks Phuoc.

Also thanks Eric W.