Finding the current duplicate record

[Stan Dad]

I have a spreadsheet to track events (servicing, repairs, fuelling, etc) for my car - dates in Col A, events in B, mileage in C, prices in D.

I want to return the mileage when the next service is due, based on the mileage of the last service. Using VLOOKUP set to FALSE returns the first service mileage; set to TRUE, it returns random values. The events in Col B repeat in a random order (e.g. fuel, fuel, service, fuel, repairs, fuel, fuel, service).

All help appreciated.

 

[jamtay317]

can you please provide some data? so we understand more of what your wanting

 

[Stan Dad]

can you please provide some data? so we understand more of what your wanting

Here is an extract from the spreadsheet:

Date	Event	Mileage	Cost
29/1/13	Fuel	61309	77.68
30/1/13	Service	61312	230.15
9/2/13	Fuel	61864	77.08
14/2/13	Repairs	62447	21.19
15/2/13	Fuel	62448	76.60

<tbody>
</tbody>

Each of the scheduled events repeats several times, and I want to return the mileage of the last occurrence of the service event. Using VLOOKUP will only return the first occurrence of that event if set to FALSE, and an irrelevant value if set to TRUE.

 

[jamtay317]

	F	G	H	I	J	K	L
2	Event lookup	count		Date	Event	Mileage	Cost
3	fuel	3		15/2/13	Fuel	62448	76.6

<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>
</tbody>

I USED =INDEX(A3:D7,LARGE(IF($B$3:$B$7=$F$3,ROW($B$3:$B$7)-ROW($B$3)+1),1),MATCH(I2,A2:D2,0)) BUT i SET UP THE EVENT LOOK UP TO THE left there and when you type Fuel in there I will look up the last one, same with repairs or what ever you type in there, this is assuming that you have your data starting in A3

 

[Stan Dad]

Thanks for this Jamtay: I'll try this and let you know how I get on.