Lookup Column, Return Max Value and Date Value

[Karl_Hungus_53]

I have a table with mileage history to various company vehicles that looks like this:

[TABLE="class: grid, width: 1414"]
<colgroup><col><col span="21"></colgroup><tbody>[TR]
[TD]Truck #[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]20[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]4/7/2017[/TD]
[TD][/TD]
[TD="align: right"]275,686[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]266,965[/TD]
[TD="align: right"]219,614[/TD]
[TD="align: right"]159,600[/TD]
[TD="align: right"]134,764[/TD]
[TD="align: right"]141,345[/TD]
[TD="align: right"]158,152[/TD]
[TD="align: right"]56,333[/TD]
[TD][/TD]
[TD="align: right"]72,254[/TD]
[TD="align: right"]61,393[/TD]
[TD="align: right"]52,866[/TD]
[TD="align: right"]47,684[/TD]
[TD][/TD]
[TD="align: right"]342,680[/TD]
[TD="align: right"]21,032[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]3/31/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]266,500[/TD]
[TD="align: right"]219,438[/TD]
[TD="align: right"]159,300[/TD]
[TD][/TD]
[TD="align: right"]140,830[/TD]
[TD="align: right"]137,667[/TD]
[TD="align: right"]56,287[/TD]
[TD][/TD]
[TD="align: right"]71,972[/TD]
[TD][/TD]
[TD="align: right"]52,506[/TD]
[TD="align: right"]47,101[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]20,598[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]3/24/2017[/TD]
[TD] [/TD]
[TD="align: right"]275,686[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]265,900[/TD]
[TD="align: right"]219,152[/TD]
[TD="align: right"]158,700[/TD]
[TD="align: right"]134,551[/TD]
[TD="align: right"]140,625[/TD]
[TD="align: right"]137,179[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]71,687[/TD]
[TD] [/TD]
[TD="align: right"]52,146[/TD]
[TD="align: right"]46,410[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]20,244[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

Each truck then has it's own tab where I have maintenance history. On each of those tabs is a cell for the most recent mileage reading and a cell with the date of that reading. I would like to create a formula for the Mileage cell to lookup the truck number and return the highest mileage reading and in the Date cell, return the date of that reading.

Example: on the tab for truck number 6, the Mileage cell should read 266,965 and the Date cell should read 4/7/2017.

I figured out the mileage issue using this formula: {=MAX(IF(1:1="Desired Truck Number", 3:5))}, but I can't get the date problem solved. Any ideas? Also, is there a better way to find the mileage?

 

[Aladin Akyurek]

1. In A10 just enter:

=MAX(INDEX($B$3:$U$5,0,6))

2. In A11 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$3:$A$5,SMALL(IF(INDEX($B$3:$U$5,0,6)=$A$10,ROW($A$3:$A$5)-ROW($A$3)+1),ROWS($A$11:A11))),"")

You can have 6 in say $A$9 and refer to it in the formulas.