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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Summary​

Array Formulas Cell Formula V7 {=MAX(IF(Logbook!$B$5:$B$111=LEFT(Summary!A7,4),Logbook!$A$5:$A$111))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[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