[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​

 

[Fluff]

I'm afraid I don't understand what you are saying and the fact that you have changed the layout of your data doesn't help.
Can you please post some sample data using the XL2BB add-in.