Using VLOOKUP With MIN, MAX, and AVERAGE - KING OF EXCEL

KING OF EXCEL

KINGEXCEL.INFO ( KING OF EXCEL ) Welcome KINGEXCEL.INFO - Nothing Is Unable ... About Excel Tricks, Learning VBA Programming, Dedicated Software, Accounting, Living Skills ...

Wednesday, December 18, 2019

Using VLOOKUP With MIN, MAX, and AVERAGE

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, MINMAX, and AVERAGE are nested to return the name of the salesperson associated with those functions.
The formula in cell D2 is =VLOOKUP(MIN(A4:A22),A4:B22,2,0)
The formula in cell E2 is =VLOOKUP(MAX(A4:A22),A4:B22,2,0)
The formula in cell F2 is =VLOOKUP(AVERAGE(A4:A22),A4:B22,2,1)
The VLOOKUP function’s fourth argument is range_lookup. For the MIN and MAX functions, the range_lookup is 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.
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1

No comments:

Post a Comment