Finding the last match in a list

[rowett]

I want to be able to find the last match in a list. To find the first match I use the MATCH worksheet function. For example MATCH("Chris", A1:A5, 0) finds the index of the first cell in the range A1:A5 containing the text "Chris".

How do I find the LAST cell in the range that contains "Chris"?

Many thanks,
Chris

 

[Aladin Akyurek]

I've been trying this for over an hour with MAX(IF()), which worked on one worksheet but not another, INDEX(MATCH()), LOOKUP()....you name it. Nothing worked but INDEX(SUMPRODUCT()) is perfect!

Perfect for what? Could you post a sample along with the desired result?

 

[soccerkingpilot]

Perfect for what? Could you post a sample along with the desired result?

I was trying to create a summary page for car maintenance log. Here is the result:

=INDEX(maint_data[#All],SUMPRODUCT(MAX((maint_data[Maintenance Type]=A17)*(ROW(maint_data[Maintenance Type])))),3)

The summary page has a list of maintenance types (oil change, tire rotation, brake inspection, etc.). On the maintenance page, I keep a running log of all maintenance actions. The summary page then displays when the last action of the type was, and calculates when the next one is due based on my manual's recommended cycle.

 

[Aladin Akyurek]

@ soccerkingpilot

Could you post 10 rows from this table (with headers included) along with the desired result for that bit of 10 rows?

 

[soccerkingpilot]

@ soccerkingpilot

Could you post 10 rows from this table (with headers included) along with the desired result for that bit of 10 rows?

SUMMARY TABLE:
[TABLE="width: 500"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Maintenance Cycles[/TD]
[TD]Schedule[/TD]
[TD]Last Service[/TD]
[TD]Next Service[/TD]
[/TR]
[TR]
[TD]Air Filter[/TD]
[TD] 12,000[/TD]
[TD]18,890[/TD]
[TD] 30,890[/TD]
[/TR]
[TR]
[TD]Oil Change[/TD]
[TD] 6,000[/TD]
[TD]18,890[/TD]
[TD] 24,890[/TD]
[/TR]
[TR]
[TD]Tire Rotation[/TD]
[TD] 6,000[/TD]
[TD]13,397[/TD]
[TD] 19,397[/TD]
[/TR]
[TR]
[TD]Cabin Air Filter[/TD]
[TD] 12,000[/TD]
[TD]14,100[/TD]
[TD] 26,100[/TD]
[/TR]
[TR]
[TD]Brake Inspection[/TD]
[TD] 12,000[/TD]
[TD]13,397[/TD]
[TD] 25,397[/TD]
[/TR]
</tbody>[/TABLE]

MAINTENANCE DATA:
[TABLE="width: 344"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Maintenance Type[/TD]
[TD]Odometer[/TD]
[/TR]
[TR]
[TD]01-Jul-18[/TD]
[TD]Oil Change[/TD]
[TD="align: right"]6,954[/TD]
[/TR]
[TR]
[TD]03-Jul-18[/TD]
[TD]Tire Rotation[/TD]
[TD="align: right"]7,015[/TD]
[/TR]
[TR]
[TD]08-Sep-18[/TD]
[TD]Oil Change[/TD]
[TD="align: right"]13,019[/TD]
[/TR]
[TR]
[TD]16-Sep-18[/TD]
[TD]Engine Air Filter Change[/TD]
[TD="align: right"]13,235[/TD]
[/TR]
[TR]
[TD]05-Oct-18[/TD]
[TD]Tire Rotation[/TD]
[TD="align: right"]13,397[/TD]
[/TR]
[TR]
[TD]05-Oct-18[/TD]
[TD]Brake Inspection[/TD]
[TD="align: right"]13,397[/TD]
[/TR]
[TR]
[TD]28-Oct-18[/TD]
[TD]Cabin Air Filter[/TD]
[TD="align: right"]14,100[/TD]
[/TR]
[TR]
[TD]18-Jan-19[/TD]
[TD]Oil Change[/TD]
[TD="align: right"]18,890[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Why 18,890 for Air Filter as there is no separate entry for this maintenance type?

 

[soccerkingpilot]

Why 18,890 for Air Filter as there is no separate entry for this maintenance type?

That's a mistake, haha

 

[Aladin Akyurek]

That's a mistake, haha

But what should be the value?

 

[soccerkingpilot]

But what should be the value?

That's the engine air filter.

 

[Aladin Akyurek]

That's the engine air filter.

Book1
	A	B	C	D	E	F	G	H	I
1	Date	Maintenance Type	Odometer			Maintenance Cycles	Schedule	Last Service	Next Service
2	1-Jul-18	Oil Change	6,954			Engine Air Filter	12,000	13,235	25,235
3	3-Jul-18	Tire Rotation	7,015			Oil Change	6,000	18,890	24,890
4	8-Sep-18	Oil Change	13,019			Tire Rotation	6,000	13,397	19,397
5	16-Sep-18	Engine Air Filter Change	13,235			Cabin Air Filter	12,000	14,100	26,100
6	5-Oct-18	Tire Rotation	13,397			Brake Inspection	12,000	13,397	25,397
7	5-Oct-18	Brake Inspection	13,397
8	28-Oct-18	Cabin Air Filter	14,100
9	18-Jan-19	Oil Change	18,890
Sheet1

In H2 enter and copy down:

=LOOKUP(9.99999999999999E+307,SEARCH(F2,maint_data[Maintenance Type]),maint_data[Odometer])