Formula to find maximum value

You can use an Excel array formula to find the find highest corresponding value for a given value in a table.

{=MAX(IF(A:A=Q2,L:L)}

So this example we are searching for where the value in cell Q2 will have the highest value in column L for the matching value in column A. Be sure to press CTRL + SHIFT + ENTER after keying in this array formula

Here we look up the abbreviation of Babe Ruth's name in column Q in the table for seasonal major league baseball statistics from 1871 to the present, to the find his highest single season home run total.