Excel Formula.

[AliaNi]

Hi I need help with a formula. I tried so many and they either return zero or spill and I don't know what's wrong with it. Xl2BB isn't working.

I want to return ID 2.

I want to look at Area Out, and then look at Area In with the same area code. Then return the ID associated with the earliest Date right from Date In after Date out.

So for ID 1, it's going to be 6.

ID	Date In	Date Out	Area In	Area Out
1​	2/19/24 8:25 AM​	2/21/24 3:37 PM​	330	330
2​	2/16/24 11:05 PM​	2/18/24 5:15 PM​	326	330
3​	1/13/24 6:52 AM​	1/15/24 6:56 PM​	330	330
4​	1/20/24 7:37 AM​	1/22/24 9:48 PM​	330	330
5​	2/25/24 12:16 AM​	2/27/24 4:07 PM​	330	330
6​	1/22/24 6:10 AM​	1/25/24 2:39 PM​	330	330

 

[StephenCrump]

I tried so many and they either return zero or spill ....

It looks like you have access to Excel 365? if so, can you please update your Account details, as solutions may vary with Excel version.

I don't understand either of these two points:

I want to return ID 2.

So for ID 1, it's going to be 6.

But based on your explanation, I am guessing:

	A	B	C	D	E	F	G
1	ID	In	Out	Area In	Area Out
2	1	19 Feb 2024 08:25	21 Feb 2024 15:37	330	330		5
3	2	16 Feb 2024 11:05	18 Feb 2024 17:15	326	330		1
4	3	13 Jan 2024 06:52	15 Jan 2024 18:56	330	330		4
5	4	20 Jan 2024 07:37	22 Jan 2024 21:48	330	330		1
6	5	25 Feb 2024 12:16	27 Feb 2024 16:07	330	330		-
7	6	22 Jan 2024 06:10	25 Jan 2024 14:39	330	330		1
Sheet1

Cell Formulas
Range	Formula
G2:G7	G2	=TAKE(SORT(FILTER(A$2:B$7,(D$2:D$7=E2)*(B$2:B$7>C2),{"-",0}),2,1),1,1)

Example: For ID #3, Area Out is 330, Time out is 15 Jan 2024 6:56 PM.
There are four IDs (1, 4, 5, and 6) with Area In =330, and Time In > 15 Jan 2024 6:56 PM.
Of these four, ID #4 has the earliest Time In.
Hence the solution shown is ID #4.