[Formula Help] Find 'Name' within separate Range and pull the lowest of multiple 'Dates' and also the corresponding 'Contract Number' for that Date

[NJ7]

My goal is to fill the yellow-highlighted table in the 'Output Table' tab.

I want to find the Nearest Contract Expiration Date (to TODAY() if possible) based on the name in Column C.

My issue is there are multiple "Color" contracts, each having their own expiration date and possibly contract number.

So if possible, I went to find "John" in the "Data Table" tab, locate the nearest of the 4 "Color" dates, and then also grab the corresponding Contract Number to that date.

Is this possible? I have tried to simplify it the best I can, but unfortunately due to the nature of the data I am afraid this is as simple as it gets.

Thank you in advance!

Output Table:

Nearest Contract Expiration Date	Contract Number for Nearest Contract Expiration Date	Name
		John
		Luke
		Bryan
		Jake

Data Table:

Blue	Yellow	Green	Red
Name	Contract	Expiration	Contract	Expiration	Contract	Expiration	Contract	Expiration
John	12345​	1/1/2025​	11234​	1/1/2026​	11123​	1/1/2028​	11112​	1/1/2027​
Luke	23456​	2/2/2026​	22345​	2/2/2028​	22234​	2/2/2027​	22223​	2/2/2025​
Bryan	34567​	3/3/2027​	33456​	3/3/2026​	33345​	3/3/2025​	33334​	3/3/2028​
Jake	45678​	4/4/2028​	44567​	4/4/2027​	44456​	4/4/2026​	44445​	4/4/2025​

 

[NJ7]

Wow - my apologies on the horrible formatting, I am unable to figure out how to post my workbook and it did not like how I copied my tables in - nor can I edit the post.

Igoring the colors at the top - Each bundle of "Contract & Expiration" equates to one of the colors. So column B and C = Blue, etc.

 

[Fluff]

Hi & welcome to MrExcel.
Do you have the dynamic array functions yet?

 

[NJ7]

Hi & welcome to MrExcel.
Do you have the dynamic array functions yet?

I do not believe so.

 

[Fluff]

Ok, that makes it more difficult, but need to check that is this what you want?

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Nearest Contract Expiration Date	Contract Number for Nearest Contract Expiration Date	Name				Name	Contract	Expiration	Contract	Expiration	Contract	Expiration	Contract	Expiration
2	01/01/2025	12345	John				Bryan	34567	03/03/2027	33456	03/03/2026	33345	03/03/2025	33334	03/03/2028
3	02/02/2025	22223	Luke				Jake	45678	04/04/2028	44567	04/04/2027	44456	04/04/2026	44445	04/04/2025
4	03/03/2025	33345	Bryan				John	12345	01/01/2025	11234	01/01/2026	11123	01/01/2028	11112	01/01/2027
5	04/04/2025	44445	Jake				Luke	23456	02/02/2026	22345	02/02/2028	22234	02/02/2027	22223	02/02/2025
Main

Cell Formulas
Range	Formula
A2:A5	A2	=MIN(FILTER(FILTER($H$2:$O$5,$G$2:$G$5=C2),$H$1:$O$1="Expiration"))
B2:B5	B2	=INDEX(FILTER($H$2:$O$5,$G$2:$G$5=C2),MATCH(A2,FILTER($H$2:$O$5,$G$2:$G$5=C2),0)-1)

 

[NJ7]

That is exactly what I am looking for - but the FILTER function does not appear to be available to me.

 

[Fluff]

Filter is one of the dynamic array functions, which you should get shortly.
In the mean time, how about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Nearest Contract Expiration Date	Contract Number for Nearest Contract Expiration Date	Name				Name	Contract	Expiration	Contract	Expiration	Contract	Expiration	Contract	Expiration
2	01/01/2025	12345	John				Bryan	34567	03/03/2027	33456	03/03/2026	33345	03/03/2025	33334	03/03/2028
3	02/02/2025	22223	Luke				Jake	45678	04/04/2028	44567	04/04/2027	44456	04/04/2026	44445	04/04/2025
4	03/03/2025	33345	Bryan				John	12345	01/01/2025	11234	01/01/2026	11123	01/01/2028	11112	01/01/2027
5	04/04/2025	44445	Jake				Luke	23456	02/02/2026	22345	02/02/2028	22234	02/02/2027	22223	02/02/2025
Main

Cell Formulas
Range	Formula
A2:A5	A2	=MINIFS(INDEX($H$2:$O$5,MATCH(C2,$G$2:$G$5,0),0),$H$1:$O$1,"Expiration")
B2:B5	B2	=INDEX($H$2:$O$5,MATCH(C2,$G$2:$G$5,0),MATCH(A2,INDEX($H$2:$O$5,MATCH(C2,$G$2:$G$5,0),),0)-1)

 

[NJ7]

Filter is one of the dynamic array functions, which you should get shortly.
In the mean time, how about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Nearest Contract Expiration Date	Contract Number for Nearest Contract Expiration Date	Name				Name	Contract	Expiration	Contract	Expiration	Contract	Expiration	Contract	Expiration
2	01/01/2025	12345	John				Bryan	34567	03/03/2027	33456	03/03/2026	33345	03/03/2025	33334	03/03/2028
3	02/02/2025	22223	Luke				Jake	45678	04/04/2028	44567	04/04/2027	44456	04/04/2026	44445	04/04/2025
4	03/03/2025	33345	Bryan				John	12345	01/01/2025	11234	01/01/2026	11123	01/01/2028	11112	01/01/2027
5	04/04/2025	44445	Jake				Luke	23456	02/02/2026	22345	02/02/2028	22234	02/02/2027	22223	02/02/2025
Main

Cell Formulas
Range	Formula
A2:A5	A2	=MINIFS(INDEX($H$2:$O$5,MATCH(C2,$G$2:$G$5,0),0),$H$1:$O$1,"Expiration")
B2:B5	B2	=INDEX($H$2:$O$5,MATCH(C2,$G$2:$G$5,0),MATCH(A2,INDEX($H$2:$O$5,MATCH(C2,$G$2:$G$5,0),),0)-1)

First - thank you so much. These do infact work and I am able to get the information as required.

Second - I have been running into issues when sorting other columns. These formula don't 'stay' with the cells. For example, in the above table, if I sort column "D" smallest to largest, the formula in cell A2 would still be linking to cells in line 4 - does that make sense?

I have tried a few different ways to amend this, but the whole idea of using these formulas is to get a "days to expiration" using the date it is pulling, so the table will constantly be sorting "Days to expiration" from smallest to largest when new data is pasted in.

 

[Fluff]

The formulae are tied to the name in col C, if you sort col C the formulae will continue to pull information for that person.

 

[NJ7]

So I actually doubled the formula in Column B, so I have these formulas in Columns A, B, and C.

What you are saying is I can't sort by any other column other than the columns these formulas pull from?