Get value from another table using date reference and unique id

[BradleyS]

I need to show the latest value from sheet 2 in sheet1 column C
Only if the sheet2 date is >= the sheet1 date for the same id

I have highlighted the values in the sheet 1 table of column c to demonstrate the outcome I'm after.
The bit I'm struggling with in a formula is only using the latest date from sheet 2 for the unique id

Does anyone have any ideas on what formula I can use? Thank you

 

[AlanY]

try this

Book1
	A	B	C	D	E	F	G
1	id	date	value		id	date	value
2	1	08/03/2024	0.2		1	27/03/2024	0.2
3	3	07/03/2024			1	27/03/2023	0.3
4	25	22/04/2024	0.8		3	06/02/2024	0.1
5	23250	08/04/2024			25	16/07/2024	0.8
6					25	23/04/2024	0.7
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	=IF(MAX(IF($E$2:$E$6=A2, $F$2:$F$6))<B2,"",SUMIFS($G$2:$G$6,$E$2:$E$6,A2,$F$2:$F$6,MAX(IF($E$2:$E$6=A2, $F$2:$F$6))))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[myall_blues]

Or this in the value column of sheet1. Obviously change cell references to suit.

=VLOOKUP(MAXIFS(Sheet2!B$2:B$6,Sheet2!A$2:A$6,$A2),Sheet2!B$2:C$6,2,FALSE)

Although just realised I missed the bit about sheet2 date >= sheet1 date.

 

[BradleyS]

I'm still using Excel 2010 (I know!) so the MAX formula works for me. I would never have worked that one out.

If I wanted to use the earliest date from sheet 2 (as long as it was still after the date in sheet 1, what part of the formula would I need to change?
Is it the MAX to MIN?

 

[BradleyS]

Changing MAX to MIN seems to have worked. I was just checking that I haven't missed anything.

 

[myall_blues]

So instead try:
=IFERROR(VLOOKUP(MAXIFS(Sheet1!G$2:G$6,Sheet1!F$2:F$6,$A2,G$2:G$6,">="&$B2),Sheet1!G$2:H$6,2,FALSE),"")

 

[BradleyS]

I was trying to enter the date from table 2 in column D by adjusting this formula:
=IF(MAX(IF($E$2:$E$6=A2, $F$2:$F$6))<B2,"",SUMIFS($G$2:$G$6,$E$2:$E$6,A2,$F$2:$F$6,MAX(IF($E$2:$E$6=A2, $F$2:$F$6))))

to this:
=IF(MAX(IF($E$2:$E$6=A2, $F$2:$F$6))<B2,"",SUMIFS($F$2:$F$6,$E$2:$E$6,A2,$F$2:$F$6,MAX(IF($E$2:$E$6=A2, $F$2:$F$6))))

but it sends back a strange date. What do I need to do to show the date of the matching value?