How to make a between function in Excel 2016?

Question

I use Excel 2016, and I have two tables, a “reference” table, with a start, end and activity column. And a “query” table, which has columns with the same names.

The start and end are already filled in, but I need a formula for the “activity”, just like in the following example:

Reference		Formula
From	To	Activity		From	To	Activity
13:00	13:12	AWAITING LABOURS		13:00	13:12	AWAITING LABOURS
13:12	13:37	CHECK-LIST		13:12	13:37	CHECK-LIST
13:37	15:15	WORKING		13:37	13:41	WORKING
15:15	15:22	AWAITING CARGO		13:41	14:02	WORKING
				14:02	15:15	WORKING
				15:15	15:22	AWAITING CARGO

Answer 1

Hi,

In cell G3, enter this formula

=INDEX($C$3:$C$6,MATCH(1,(($A$3:$A$6<=E3)*($B$3:$B$6>=F3)),0),1)

Hope this helps.

Answer 2

Hi Matheus Dos Reis Fraga ,

Thanks for visiting Microsoft Community.

Assuming the data starts from column A, you may input the following formula in the target cell:

=IFERROR(INDEX(C:C, AGGREGATE(15, 6, ROW(A$2:A$100)/( (A$2:A$100 <= E2) * (B$2:B$100 >= F2) ), 1)), "")

or in PT-BR version:

=SEERRO(ÍNDICE(C:C; AGREGAR(15; 6; LIN(A$2:A$100)/( (A$2:A$100 <= E2) * (B$2:B$100 >= F2) ); 1)); "")

Subsequently, apply this formula by dragging it down to all relevant rows. The resulting output will resemble the illustration below:

This solution should effectively address your requirements.

Best Regards,

Jonathan Z - MSFT | Microsoft Community Support Specialist