Insert value into corresponding cell based on item & date

[JansiJansi]

Hi all.

Here's the TL;DR of what I'm doing:
Taking a list of receive dates for items received in the past year, and putting that date in its corresponding bracket. Here's how it looks (ignore the data - it's not specific, just for example):

The way I'm finding the date of the age is by taking a receiving report, finding the most recent receive date for the SKU/item, and subtracting it from =TODAY(). I end up getting a number like 388 (after rounding) for 7/30/2021.

I need the table above to know that skux, for example, was last received 388 days ago, and insert the date 7/30/2021 into the "Age 366+" column in skux's row, etc.

Does anyone have a complicated formula that could accomplish this?

Thank you!

 

[jorismoerings]

Hi,

try something like this with a helper column added.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	sku	title	date	Ageing Band			SKU	Title	OH Q	Age 0-90	Age 91-180	Age 181-270	Age 271-365	Age 366+
2	sku1	title1	1-1-2021	5,00			sku1	title1					12-11-2021
3	sku1	title1	1-2-2021	5,00			sku2	title2						30-7-2021
4	sku1	title1	1-3-2021	5,00
5	sku1	title1	1-4-2021	5,00
6	sku1	title1	1-5-2021	5,00
7	sku1	title1	2-6-2021	5,00
8	sku1	title1	3-6-2021	5,00
9	sku1	title1	4-8-2021	5,00
10	sku1	title1	5-9-2021	4,00
11	sku1	title1	6-9-2021	4,00
12	sku1	title1	10-11-2021	4,00
13	sku1	title1	11-11-2021	4,00
14	sku1	title1	12-11-2021	4,00
15	sku2	title2	30-7-2021	5,00
Sheet1

Cell Formulas
Range	Formula
G2:H3	G2	=UNIQUE(A2:B15)
J2:N3	J2	=IF(COLUMN()-9=MINIFS($D$2:$D$15,$A$2:$A$15,$G2,$B$2:$B$15,$H2),MAXIFS($C$2:$C$15,$A$2:$A$15,$G2,$B$2:$B$15,$H2),"")
D2:D15	D2	=LOOKUP(TODAY()-C2,{0;90;180;270;365},{1;2;3;4;5})
Dynamic array formulas.