VLOOKUP() to Return the Min Max or Average Value in Excel

How to make a VLOOKUP() function in Excel that returns the minimum, maximum, or average value from a list or table of data. This tutorial also includes a more robust lookup that uses the INDEX() and MATCH() functions.

This tutorial introduces a few concepts related to lookup functions in Excel and how to combine them with other functions in order to perform more advanced lookups that dynamically return values based on a predefined set of criteria, such as:
- Return the largest value from a list with a vlookup or an index/macth lookup.
- Return the smallest value from a list with a vlookup or an index/macth lookup.
- Return the average, or closest to average value, from a list using a vlookup or an index/macth lookup.

As well as the VLOOKUP() function section, this tutorial will also show you how to return the min, max, and average values using the INDEX/MATCH type lookup, which allows you to build a more robust lookup that returns data from anywhere in the data set.

I hope you enjoy this tutorial!

#excel #tutorial #howto

[toxiqrox]

You have no idea the world you are opening up for me. I still like the easier parts of your tutorials best.

[leelundang]

what if the numbers are not on the most lest side.. what formula can be use?

[deepstudios]

Thank you for this upload!

Is there a way to report ALL instances of a max or min value. E.g. If I have a dataset that has a max value of 20, but that value appears 17 times throughout the dataset, is there a way to find all 17 rows that it appears at?

[isaotojo]

I was wondering if there could be a way to return the row number of the output from Vlookup.
With an extra column indicating row number I made it. Although this is away from the point of the subject your lesson gave me a useful hint.

[abbottkatz]

Of course, with dynamic array functions you can go on to write VLOOKUPs like this, e.g. =VLOOKUP(LARGE(A5:A9, {1, 2, 4}), tablearray, 2)
and return multiple answers in multiple cells.

[darrylmorgan]

Great Tutorial With Some Really Cool Formulas...Thank You Sir :)

[yasminelsobky]

I've a group of patients(pts) taking many medications (each pts taking 4+ medications).
I want the excel to calculate the "nearest date of discontinue medication" for each drug in relation to another date "date of surgery". How can I do that?

[punitranjan]

Dear sir,
I want average of four number excluding upper and lower number.
Example
10, 20, 30, 40
Average is 25

[manuvincent]

Aint the next highest value to 4.6...5
Why it went for 3 in ascending
And 1 in descending

[patrickschardt]

Even with it’s known issues (sorted, no left lookup, etc) VLOOKUP is still one of the most powerful Excel functions