Forum Discussion
max value if criteria match
Dear all,
I hope you can help me with this.
I am trying to generate a spreadsheet that can keep track of fuel consumption. The one responsible for this will enter the information related to mileage once as the vehicles are LEAVING the premises. He will record the mileage again the next time the truck leaves again. The second time he records the mileage, will be the returning mileage for the previous trip, therefore we can record the total mileage for the first trip. In this report I would like to match the truck's fleet number, record the next trip's INITIAL KM and automatically fill in the previous trip's FINAL KM. I hope this makes sense and I hope it's possible. Sample of the sheet is attached, I'm using Excel 2007.
Many Thanks
Vito
11 Replies
As variant
=INDEX($C$2:$C$1000,AGGREGATE(14,6,1/($B$2:$B$1000=$B2)*(ROW($B$2:$B$1000)-ROW($B$1)),1))gives
Thank you both so much, it worked.
Really appreciate your quick responses.
Vito
mtarler Could I please ask you for another favor?
I have attached the full data input sheet. Apart from tracking mileage, I'm looking also at regularly measuring any reserve left in the fuel tank. Each driver should keep 40 litres of fuel in the tank at all times and this is measured by dipping a level stick into the calibrated tank. Every time a truck goes out, the stick is dipped and we take a record of the level. Would be great if you could help me with a similar formula to match the fleet number with a previous trip and calculating the "float" variance for that particular trip.
The columns for float and any variance are H and I in the attached file...
Many Thanks
Vito
- It sounds like you want the exact same thing but applied to columns H and I, so the same formula could be used but expand the table to include column H and change the 2 to a 5 (to indicate you want the 5th column returned instead of the 2nd):
=VLOOKUP(B2,B3:H$99999,5,FALSE)
- Thank you so much!
Afraid XLOOKUP() doesn't work on Excel 2007.
- yea, I notice right after I hit post
