Forum Discussion
Populating cell with value in immediate criteria match
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
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).
7 Replies
- 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)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).
- 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?
