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

Add to Favorites

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)

e3b71f5627c68267fc4f3319b4b72d26.png

Result:

52a823924f37b7bc2d0dc8e4bbb1500c.png

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)

bc4cf0b24a060209a261a89b9d669f9c.png

Result:

5eec925ff1a28a070b7ca00014eb860a.png

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)

a056da0dc4dafcad29651f21d282bb02.jpg

Result:

17e0edcf84e4fb2cd0bd8f17ef9e78bf.jpg

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.


Excel Function: AVERAGE(), MAX(), MIN(), VLOOKUP()
Downloadable Files: Excel File

Similar Content on TeachExcel
Filter Data to Show the Bottom X Number of Items in Excel - AutoFilter
Macro: This free Excel macro filters a data set to show the bottom X number of items from tha...
Filter Data to Show the Top X Number of Items in Excel - AutoFilter
Macro: This Excel macro filters a data set to display only the top X number of items in that data...
Return the Min or Max Value Using a Lookup in Excel - INDEX MATCH
Tutorial: Find the Min or Max value in a range and, based on that, return a value from another rang...
Vlookup Macro to Return All Matching Results from a Sheet in Excel
Macro: This Excel Macro works like a better Vlookup function because it returns ALL of the matchi...
Extract the Last Word from a Cell in Excel - User Defined Delimiter Text Extraction - UDF
Macro: This UDF (user defined function) extracts the last word or characters from a cell in Excel...
Get the First Word from a Cell in Excel
Tutorial: How to use a formula to get the first word from a cell in Excel. This works for a single c...