Vlookup to Return the Min, Max, or Average Value in Excel

Perform a Vlookup that returns the highest value, lowest value, or average value from a dataset.

Sections:

Vlookup to Return Max

Vlookup to Return Min

Vlookup to Return Average

Notes

Vlookup to Return Max

Return the max value from a table of data.

=VLOOKUP(MAX(A1:A5),A1:B5,2,FALSE)

Result:

This Vlookup function is exactly the same as the regular one except that the MAX() function is used for the lookup value argument.

The MAX() function returns the highest value from the list of numbers and then that value is used to perform the lookup. Otherwise, this is a standard Vlookup function.

Vlookup to Return Min

Return the min value from a table of data.

=VLOOKUP(MIN(A1:A5),A1:B5,2,FALSE)

Result:

The MIN() function is used in this example to find the smallest value from the list of numbers; then, that value is used to perform the lookup in the lookup_value argument. Otherwise, this is a standard Vlookup function.

Vlookup to Return Average

Return the average value from a table of data.

=VLOOKUP(AVERAGE(A1:A5),A1:B5,2,TRUE)

Result:

The AVERAGE() function is used in the lookup_value argument in order to find the average value from the list of numbers; then, that value, which is the average, is used to perform the lookup.

Note: in this example the last argument, range_lookup, is set to TRUE. This is because the average function can return a value that is not in the list of values and so you need the Vlookup function to find the next highest value. In this example, it doesn't matter, but, if you want to check it out, change the 5 in row 5 to 12 and the TRUE value in cell D3 to FALSE and you will get an #N/A error.

Notes

Vlookups can be really helpful once you get used to using them and this is just one more way you can make them work for you to quickly return relevant and useful data.

Make sure to download the sample file for this tutorial so you can work with these examples in Excel.