Lookup of the latest date arrival date

[ExcelNewbie2020]

Sirs/Ma'ams,


I have a long range of column table, in which, dates as column header. Under each column, there is number 2=Arrival, 1=Departure and 3=whole day.

I would like to lookup for the latest date of arrival, if the return value is a column header?..

SAMPLE.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
1			2= ARRIVAL
2			1= DEPARTURE
3			3=WHOLE DAY
4			BLUE=CURRENT DATE
5				AUGUST 2022
6	NAME	#	DEPT	01	02	03	04	05	06	07	08	09	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31
7	NAME 1	1	HR																2	3	3	3	3
8	NAME 2	2	HR																	2	3	3	3
9	NAME 3	3	HR	2	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3
10	NAME 4	4	HR
11	NAME 5	5	HR	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	1
12	NAME 6	6	ADMIN
13	NAME 7	7	ADMIN
14	NAME 8	8	ADMIN	3	3	1
15	NAME 9	9	ADMIN																	2	3	3	3
16	NAME 10	10	OPERATIONS										2	3	3	3	3	3	3	1
17	NAME 11	11	OPERATIONS	3	3	3	3	3	3	3	3	3	1
18	NAME 12	12	OPERATIONS	3	3	3	3	3	3	3	1							2	3	3	3	3	3
19	NAME 13	13	OPERATIONS			2	3	3	3	3	3	3	3	3	3	3	3	3	3	1
20	NAME 14	14	OPERATIONS
21	NAME 15	15	OPERATIONS	3	3	3	3	3	3	3	3	3	3	3	3	1
22	NAME 16	16	ACCTG										2	3	3	3	3	3	3	3	3	3	3
23	NAME 17	17	ACCTG	3	3	1														2	3	3	3
24	NAME 18	18	ACCTG			2	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3
25	NAME 19	19	ACCTG			2	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3
26	NAME 20	20	ACCTG			2	3	3	3	3	3	3	1
27	NAME 21	21	ACCTG	3	3	1														2	3	3	3
28	NAME 22	22	WHSE			2	3	3	3	3	3	3	3	3	3	3	3	3	3	1
29	NAME 23	23	WHSE										2	3	3	3	3	3	3	3	3	3	3
30	NAME 24	24	WHSE			2	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3
31	NAME 25	25	WHSE																	2	3	3	3
32	NAME 26	26	WHSE	1
33	NAME 27	27	MEDICAL			2	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3
34	NAME 28	28	MEDICAL	3	3	3	3	3	3	3	1
35	NAME 29	29	MEDICAL			2	3	3	3	3	3	3	3	3	3	3	3	3	1
36
37
38
39	RESULT
40		#	DATE ARRIVE
41		1	16-08-22
42		2	17-08-22
43		3	01-08-22
44		4
45		5
46		6
47		7
48		8
49		9	17-08-22
50		10	10-08-22
51		11
52		12	15-08-22
53		13	03-08-22
54		14
55		15
56		16	10-08-22
57		17	17-08-22
58		18	03-08-22
59		19	03-08-22
60		20	03-08-22
61		21	17-08-22
62		22	03-08-22
63		23	10-08-22
64		24	03-08-22
65		25	17-08-22
66		26
67		27	03-08-22
68		28
69		29	03-08-22
Sheet1

 

[Fluff]

How about in C41 dragged down

=IFNA(INDEX($D$6:$AH$6,XMATCH(2,INDEX($D$7:$AH$35,B41,),0,-1)),"")

 

[ExcelNewbie2020]

How about in C41 dragged down

=IFNA(INDEX($D$6:$AH$6,XMATCH(2,INDEX($D$7:$AH$35,B41,),0,-1)),"")

thank you fluff for the immediate reply.. it's almost ok, can we add the "#" column as additional criteria range for the lookup?..thanks

 

[Fluff]

As the numbers are effectively row numbers, it's already doing that.

 

[ExcelNewbie2020]

As the numbers are effectively row numbers, it's already doing that.

oohh.. my mistake.. it supposed to be an employee #.

 

[Fluff]

But the numbers consecutive & in order as shown on you sample?

 

[ExcelNewbie2020]

But the numbers consecutive & in order as shown on you sample?

Yes, i made it consecutive for easy reference.. but it was supposed to be an employee number with a different sequence..

 

[Fluff]

Yes, i made it consecutive for easy reference

That is not a good idea, the data you post should be representative of your real data, otherwise you get formulae that won't work.

 

[Fluff]

Try

=IFNA(INDEX($D$6:$AH$6,XMATCH(2,INDEX($D$7:$AH$35,MATCH(B41,$B$7:$B$35,0),),0,-1)),"")

 

[alansidman]

Here is an alternative solution using Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"NAME", "#", "DEPT"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] = 2)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Value"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Attribute", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Attribute", "Arrival"}})
in
    #"Renamed Columns"

NAME	#	DEPT	Arrival
NAME 1	1	HR	8/16/2022
NAME 2	2	HR	8/17/2022
NAME 3	3	HR	8/1/2022
NAME 9	9	ADMIN	8/17/2022
NAME 10	10	OPERATIONS	8/10/2022
NAME 12	12	OPERATIONS	8/15/2022
NAME 13	13	OPERATIONS	8/3/2022
NAME 16	16	ACCTG	8/10/2022
NAME 17	17	ACCTG	8/17/2022
NAME 18	18	ACCTG	8/3/2022
NAME 19	19	ACCTG	8/3/2022
NAME 20	20	ACCTG	8/3/2022
NAME 21	21	ACCTG	8/17/2022
NAME 22	22	WHSE	8/3/2022
NAME 23	23	WHSE	8/10/2022
NAME 24	24	WHSE	8/3/2022
NAME 25	25	WHSE	8/17/2022
NAME 27	27	MEDICAL	8/3/2022
NAME 29	29	MEDICAL	8/3/2022