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:

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

<colgroup><col><col span="21"></colgroup><tbody>
</tbody>

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.