Populating cell with value in immediate criteria match

[vitoaiaco]

Dear all,

I have attached a spreadsheet containing raw data set for a fuel control spreadsheet.

We have 4 types of vehicles:

- Trucks, we dispense the exact amount of fuel according to the destination. The file works out the usage for those vehicles, therefore doesn't require attention

- Earthmoving, Buses & Other vehicles, we give fuel by completely filling up the tank. We are only able to know the usage the next time they refill.

In the case of the Earthmoving, Buses & Other vehicles, I would like to automatically populate column M ("Actual Volume Used") with the next entry for the same vehicle. Therefore we need a criteria match in column E ("Vehicle Fleet Number") and populate the cell with the next volume reading (column L) IF cells in column Q are "Earthmoving", "Buses" or "Other Vehicles". If there's no criteria match, we can keep the same formula currently present.

I hope this makes sense, any help would be hugely appreciated. @mtarler 

Many Thanks, Vito

[#2]

I have tried the following formula in column M, but it doesn't seem to work:

=IF(OR(Q5="EARTHMOVING",Q5="BUSES"),(VLOOKUP(E5,E6:I$9999,8,FALSE)),L5-K5)

[#3 · Accepted solution]

@vitoaiaco That formula breaks because you are trying to look up the 8th column in a range that contains only 5 columns (E through I).

[#4]

Thank you for the reply. I have expanded my lookup range from E:L. It appeared to popular the cell correctly, but if I change the volume value it doesn't automatically change the value in column M. Would you have any idea why this happens?

[#5]

@vitoaiaco Sorry, don't follow. Cell M9 will return 119.00 with the adjusted formula. This is the number found in L19 for vehivle number "LD 02". Correct? What volume (e.g. in which column/cell) doesn't change the value in M?

[#6]

@Riny_van_Eekelen Yes, that's correct... As it stands, Cell M9 will CORRECTLY return 119.00 with the adjusted formula. Let's say you change the value in L19 to 200 instead of 119. The value in M9 will still be 119.00 UNLESS I manually refresh the formula in M9. Is there a way to automatically refresh the formula to populate according to the latest entry?

Many Thanks

Vito

[#7]

Apologies, I had changed to calculation options to manual and therefore not automatically refreshing the data. It's now working, THANK YOU!

[#8]

@vitoaiaco Was just writing a response along those lines. You're welcome!