# Find Previous Match

#### dallen8028

##### New Member
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

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

#### ravisingh

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

#### DanteAmor

##### Well-known Member
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 />

Last edited: