# Aggregated Function over VLookup on Range

Is there a way to get a maximum value of a range of values using standard Excel functions?

• I want to find the maximum value in a range of cells. A1: `=max(A2:A10)`
• However, I don't want to find the max of the value in the cells themselves, but rather of an associated set of values obtained by doing a vlookup on those cells. i.e. B2: `=vlookup(A2,MyLookupName,2,false)` (copy B2 formula down to B10). A1: `=max(B2:B10)`.
• Rather than having to do this in 2 steps (i.e. doing the vlookup in column B, then doing the max over that in cell A1) I'd rather have a single formula which applied both steps in one, in the same cell. i.e. A1: `{=max(vlookup(A2:A10,MyLookupName,2,false))}`

However, this last formula doesn't work, as the VLOOKUP function only accepts a single cell for the `Lookup_Value` parameter; if a range is passed, it takes the top left cell from that range.

VBA Solution

To better illustrate the functionality I'm after, I've coded a VBA worksheet function which would do what I'm after; however I'm trying to avoid custom macros.

``````Public Function MaxVLookup(ByRef Lookup_Array As Range, ByRef Table_Array As Range, Col_Index_Num As Long) As Variant
Dim v As Variant, temp As Variant, max As Variant
max = Null
For Each v In Lookup_Array
temp = WorksheetFunction.VLookup(v, Table_Array, Col_Index_Num, False)
If IsNull(max) Then
max = temp
Else
If max < temp Then
max = temp
End If
End If
Next
MaxVLookup = max
End Function
``````

If the values in A are in the Reference Area only once then you can use SUMUIF() wrapped in a MAX() as an array formula:

``````=MAX(SUMIF(D:D,A1:A10,E:E))
``````

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting Edit mode. If done properly then Excel will put `{}` around the formula.

If they are multiples and you want only the first like VLOOKUP, use LOOKUP():

``````=MAX(IFERROR(LOOKUP(A1:A10,D1:D13,E1:E13),-1E+99))
``````

Again this is an array formula.

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting Edit mode. If done properly then Excel will put `{}` around the formula.

Screenshot based on example

NB: I found an alternate solution to my issue. Since I only needed to map each value to a number, I was able to fake the VLookup by using Match.

i.e. A1: `{=max(6-match(A2:A10,MyLookupName,0))}`.

The `6-` is simply to change the value returned by match (i.e. the position of the element I'm looking for in the `MyLookupName` list of values) to the value I'm mapping to.

Demo Screenshot

Solution with Values:

Solution with Formulae:

NB: I've not marked this an an answer, since though it resolved my problem, it doesn't solve all problems of this type / bypasses the answer to the question I'd asked.