Date Lookup Function - Flight Logbook.

[Tacitus1991]

Good Day,

I am a commercial pilot and I am developing my own flight logbook sheet (I know there are 1000'ds out there, but I want my own.)

I have it fine tuned, I just need some help with what I think is a VLOOKUP function, but I am not sure.

So what I have done is I have 2 Tabs, Tab 1 is the actual Logbook and Tab 2 is a summary page that generates a summary as you populate the logbook.

I need to get a formula where the summary looks at a specific column, and looks for the last time I flew a specific type of aircraft, then looks at the date next to that flight and returns that value. I am bamboozled. I have a link to the logbook (You are free to use it if you wish):

https://drive.google.com/file/d/1SGOVHWbSos-7TN56eASooChkFSc9WHZn/view?usp=sharing

Please help.

 

[AlanY]

try this

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	V
1	Name:		Petrus L. Weinrebe				Date Compiled:
2	License Number:		PA 72000
3	(01)	(02)	(03)	(04)	(05)	(06)	(07)	(08)	(09)
4	Aircraft Class or Type	Instrument		Single Engine Aircraft						Date Last Flown
5		Actual Time	FSTD Time	Day				Night
6				Dual	PIC	PICUS	Co-Pilot	Dual	PIC
7	SLG4	10.5	0	61.7	70.3	0	0	8	0.8	2018-06-12
8	RedBird	0	0.3	0	0	0	0	0	0	2017-03-04
9	C182	0	0	10.9	3.1	0	0	2.1	0	2018-06-29
10
11
12
13
14
15
16
17
18
19
20
21
22
23	Totals:	10.5	0.3	72.6	73.4	0	0	10.1	0.8
24	Total Flight Hours Colums (02) to (19)					167.7
Summary

Cell Formulas
Range	Formula
V7	{=MAX(IF(Logbook!$B$5:$B$111=LEFT(Summary!A7,4),Logbook!$A$5:$A$111))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Tacitus1991]

Hi,

Thanks.....however, All it did was throws out a random number and not a date

try this

Excel 2013/2016

	A	B	C	D	E	F	G	H	I	V
Name:		Date Compiled:
License Number:
SLG4
RedBird
C182

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]Petrus L. Weinrebe[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]PA 72000[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"](01)[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"](02)[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"](03)[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"](04)[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"](05)[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"](06)[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"](07)[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"](08)[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"](09)[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"]Aircraft Class or Type[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"]Instrument[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"]Single Engine Aircraft[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"]Date Last Flown[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"]Actual Time[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"]FSTD Time[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"]Day[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"]Night[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"]Dual[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"]PIC[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"]PICUS[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"]Co-Pilot[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"]Dual[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"]PIC[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] , align: center"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]10.5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]61.7[/TD]
[TD="align: right"]70.3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0.8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=EBF1DE]#EBF1DE[/URL] , align: center"]2018-06-12[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0.3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=EBF1DE]#EBF1DE[/URL] , align: center"]2017-03-04[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]10.9[/TD]
[TD="align: right"]3.1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2.1[/TD]
[TD="align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=EBF1DE]#EBF1DE[/URL] , align: center"]2018-06-29[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] "]Totals:[/TD]
[TD="align: center"]10.5[/TD]
[TD="align: center"]0.3[/TD]
[TD="align: center"]72.6[/TD]
[TD="align: center"]73.4[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]10.1[/TD]
[TD="align: center"]0.8[/TD]
[TD="align: center"][/TD]

[TD="align: center"]24[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] "]Total Flight Hours Colums (02) to (19)[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] "][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=BFBFBF]#BFBFBF[/URL] "][/TD]
[TD="align: center"]167.7[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Summary​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]V7[/TH]
[TD="align: left"]{=MAX(IF(Logbook!$B$5:$B$111=LEFT(Summary!A7,4),Logbook!$A$5:$A$111))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[AlanY]

you need to format the cell/column as dates

 

[Joe4]

All it did was throws out a random number and not a date

It is important to understand how dates are stored in Excel. They are really just formatted numbers (with date formats).
The number represents the number of days since 1/0/1900 (and any decimal is time, or a fraction of the day).
This is easy to see if you enter any date/time in a cell, and change the format to "General".

 

[AlanY]

is that what you got

Excel 2013/2016
	T	U	V
1
2
3	(20)	(21)
4	Landings & Take-Offs		Date Last Flown
5
6	Day	Night
7	188	16	43263
8	1	0	42798
9	56	6	43280
10
Summary

Cell Formulas
Range	Formula
V7	{=MAX(IF(Logbook!$B$5:$B$111=LEFT(Summary!A7,4),Logbook!$A$5:$A$111))}
Press CTRL+SHIFT+ENTER to enter array formulas.

after formatted as dates

Excel 2013/2016
	T	U	V
1
2
3	(20)	(21)
4	Landings & Take-Offs		Date Last Flown
5
6	Day	Night
7	188	16	12/06/2018
8	1	0	04/03/2017
9	56	6	29/06/2018
10
11
Summary

Cell Formulas
Range	Formula
V7	{=MAX(IF(Logbook!$B$5:$B$111=LEFT(Summary!A7,4),Logbook!$A$5:$A$111))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Tacitus1991]

Works now, thanks too much guys.

 

[AlanY]

you're welcome