Looking up two values to return a third

[rob412]

G'day!
I need some help to calculate the 'Hours' (F Column). Each time the servicing is completed on the engine, columns A through to E will be manually entered. Columns F and G will be automatically populated. The 'Hours' column is calculated by locating the previous servicing for the specific engine then subtracting it from the current engine hours. This will give the hours between the servicing.
Example:
When the current hours for eng002 is entered in D8 (300 in the example), cell F8 will look up the previous hours of the servicing (D4), then subtract it from the current hours (giving an answer of 180).

I've tried various versions of the VLOOKUP, INDEX, MATCH and LARGE formulae however I can't get it to work. The tricky bit is that the engine data will be entered randomly (I.e. the servicing doesn't always occur in any set order)

I hope you can understand what I'm getting at and I look forward to the solution! Below is a sample of the table that i'm trying to get to work.

Thanks!

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Engine Serial Number	Date of Servicing	Servicing Type	Engine Hours	DQ	Hours	DQ/Hr	Aircraft	Engine Position
2	eng001	01-Jan		80	90
3	eng002	10-Jan		12	60
4	eng003	12-Jan		70	80
5	eng001	30-Jan		240	40
6	eng003	09-Mar		290	160
7	eng002	04-Apr		300	200
Unknown Sheet Name

Excel 20??

 

[Peter_SSs]

Welcome to MrExcel!

As you probably saw, there was a problem with the display of your table and Jon has tried to tidy it up. If your description no longer fits the table as now displayed, just reply to thread and give a new description that does match the table as now displayed.

 

[AlphaFrog]

Excel Workbook
	A	B	C	D	E	F
1	Eng			Hrs		Hrs between service
2	Eng001			100
3	Eng002			122
4	Eng004			144
5	Eng004			166		22
6	Eng005			188
7	Eng002			210		88
8	Eng004			232		66
9	Eng001			254		154
10	Eng002			276		66
11	Eng004			332		100
Sheet

 

[Jon von der Heyden]

As you probably saw, there was a problem with the display of your table and Jon has tried to tidy it up. If your description no longer fits the table as now displayed, just reply to thread and give a new description that does match the table as now displayed.

Whoops! I lined up the columns but didn't pay attention to row references. Apologies for that.