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

**More Info**

- 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
```