0

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

Example Data

0

2 Answers 2

1

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.

enter image description here


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.

enter image description here

Screenshot based on example

Screehshot based on example

0

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 values

Solution with Formulae:

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .