Find Previous Match

[dallen8028]

Attempting to calculate Mileage difference ("D") by finding the previous "Fuel" event amongst a variety of events listed ("A") in order to fill in the missing data in Column "B" for the purpose of calculating MPG ("F")

A, B, C, D, E, F,
Event, Start, Stop, Miles, Gals, MPG,
Rvcd Vehicle, 51329.0, 51329.0, , , ,
Fuel, 51435.0, 51435.0, , 15.8, ,
Maint, 51435.0, 51465.0, 30.0, , ,
Field Trip, 51465.0, 51644.0, 179.0, , ,
Fuel, ______ , 51645.0, _____, 12.6, ,
Bus Route, 51645.0, 51665.0, 20.0, , ,
Bus Route, 51665.0, 51685.0, 20.0, , ,
field Trip, 51685.0, 52006.0, 321.0, , ,
Fuel, _______, 52007.0, _____, , ,

I hope you can read this. Thanks in advance

 

[ravisingh]

Pl use the MrExcel html maker to post the data in proper row/col format

 

[DanteAmor]

You need this?

<table style="font-family:Arial; font-size:12pt; border-style: groove ;border-color:#0000FF;background-color:#fffcf9; color:#000000; "><tr><td ><b>sheet</b></td></tr></table><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:81.74px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Event</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; "> Start</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; "> Stop</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; "> Miles</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; "> Gals</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; "> MPG</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Rvcd Vehicle</td><td style="text-align:right; ">51329</td><td style="text-align:right; ">51329</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >Fuel</td><td style="text-align:right; ">51435</td><td style="text-align:right; ">51435</td><td > </td><td style="text-align:right; ">15.8</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Maint</td><td style="text-align:right; ">51435</td><td style="text-align:right; ">51465</td><td style="text-align:right; ">30</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Field Trip</td><td style="text-align:right; ">51465</td><td style="text-align:right; ">51644</td><td style="text-align:right; ">179</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >Fuel</td><td style="background-color:#92d050; text-align:right; ">51435</td><td style="text-align:right; ">51645</td><td style="background-color:#92d050; text-align:right; ">210</td><td style="text-align:right; ">12.6</td><td style="background-color:#92d050; text-align:right; ">13.2911392</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >Bus Route</td><td style="text-align:right; ">51645</td><td style="text-align:right; ">51665</td><td style="text-align:right; ">20</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >Bus Route</td><td style="text-align:right; ">51665</td><td style="text-align:right; ">51685</td><td style="text-align:right; ">20</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >field Trip</td><td style="text-align:right; ">51685</td><td style="text-align:right; ">52006</td><td style="text-align:right; ">321</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >Fuel</td><td style="background-color:#92d050; text-align:right; ">51645</td><td style="text-align:right; ">52007</td><td style="background-color:#92d050; text-align:right; ">362</td><td > </td><td style="background-color:#92d050; text-align:right; ">28.7301587</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B6</td><td >=INDEX($C$1:$C$100,SUMPRODUCT(MAX((INDIRECT("$A$2:A" & ROW() -1)="Fuel")*ROW((INDIRECT("$A$2:A" & ROW() -1))))))</td></tr><tr><td >D6</td><td >=C6-B6</td></tr><tr><td >F6</td><td >=D6/INDEX($E$1:$E$100,SUMPRODUCT(MAX((INDIRECT("$A$2:A" & ROW() -1)="Fuel")*ROW((INDIRECT("$A$2:A" & ROW() -1))))))</td></tr></table></td></tr></table> <br /><br />