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

Author: | Edits: don

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

Excel Function: AVERAGE(), MAX(), MIN(), VLOOKUP()

#### Question? Ask it in our Excel Forum ##### Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

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 that da...
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...
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...
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...
Tutorial Details
Excel Function: AVERAGE(), MAX(), MIN(), VLOOKUP() 