Tom’s Tutorials For Excel: Using VLOOKUP With MIN, MAX, and AVERAGE
You can nest a function as the
lookup_value argument with
VLOOKUP, to return an item relating to the
lookup_value function. In the pictured example,
AVERAGE are nested to return the name of the salesperson associated with those functions.
The formula in cell D2 is
The formula in cell E2 is
The formula in cell F2 is
VLOOKUP function’s fourth argument is
range_lookup. For the
MAX functions, the
0, which could have also been written as
FALSE or simply omitted altogether. This deals with your desire and expectation that an exact lookup value will be found. In this example, you know that a specific minimum and maximum number will be found in the list in column A.
However, you cannot be assured that the average of those numbers will specifically exist in the list. The average of the numbers in column A is 5218, not found in column A, so, the
1 (which could have been written as
TRUE) was necessary in order for the formula to look up an approximate match.