You probably know the MAX function and typically only use it to find the largest value. But MAX can also act as a lookup function — solving problems VLOOKUP can't. What can MAX do that VLOOKUP cannot? Read on to find out...
More useful than VLOOKUP, yet you only use MAX to get the maximum?
No.
- Because VLOOKUP can only return the first matching result from top to bottom.
- For example, if you enter =VLOOKUP(D2,A1:B13,2,FALSE), it will only return 5/7/2019 because A2's Peter is the first match from top to bottom.
- So VLOOKUP is not suitable.
Formula:
=MAX(($A$2:$A$13=D2)*$B$2:$B$13) then press Ctrl + Shift + Enter
Understanding this formula is the main concern. The principle is simple: first perform a comparison to see which salespeople in Column A match the salesperson we want to check — that is the role of $A$2:$A$13=$D2. Select this part of the formula in the formula bar and press F9 to see the calculation result.
Next, multiply this array of logical values by the sale dates in Column B (dates are numeric in Excel). TRUE behaves like 1 in calculations and FALSE behaves like 0, so the results look like this.
In the resulting numbers, 0 indicates entries where no matching dealer was found, while the non-zero numbers are the sale dates returned for matched salespeople. The largest of these values is the most recent date, so MAX easily returns the final result.
If your result appears as a number rather than a date, change the cell format to Date.
Next, multiply this array of logical values by the sale dates in Column B (dates are numeric in Excel). TRUE behaves like 1 in calculations and FALSE behaves like 0, so the results look like this.
In the resulting numbers, 0 indicates entries where no matching dealer was found, while the non-zero numbers are the sale dates returned for matched salespeople. The largest of these values is the most recent date, so MAX easily returns the final result.
If your result appears as a number rather than a date, change the cell format to Date. 
