Compare values in 7 different cells, return description of the one with the highest value.

[jaihawk8]

I have a spreadsheet, where I am trying to compare 7 different values (non-sequential) and return the description of the one with the highest value.

This is what I'm working with:

I'm trying to compare the values in F19, F30, F35, F36, K15, and K22. Which ever one has the highest value, I want it to return the description of that one. For instance, in this case, I would want it to return "Impress Distribute & Automate Subscription). If there is no higher value (i.e. they are all $0), then just return a blank space.

Is this doable?

 

[JamesCanale]

This isn't great. I used helper cells. I couldn't figure out how to create a disjoint range within a formula. (That's a question to ask here.)

all zeros will return blank. If there is a tie, the first one will be selected.

Book1 (version 1).xlsb
	P	Q	R
1	5	Tracking Sub…	Parcel Locker
2	7	Neoship sub…
3	9	OMS - E-cert…
4	4	YayPay sub…
5	11	Enterprise Cloud…
6	23	Parcel Locker
Sheet7

Cell Formulas
Range	Formula
P1	P1	=F19
Q1	Q1	=B19
R1	R1	=IF(MAX(P1:P6)>0,VLOOKUP(MAX(P1:P6),P1:Q6,2,FALSE),"")
P2	P2	=F30
Q2	Q2	=B30
P3:P4	P3	=F35
Q3:Q4	Q3	=B35
P5	P5	=K15
Q5	Q5	=H15
P6	P6	=K22
Q6	Q6	=H22

 

[kweaver]

I adapted a UDF from someone and made minor changes:

Function MatchNonContig(LookupVal As Variant, ParamArray LookupRng() As Variant)
    Dim i As Long, j As Long
    For i = 0 To UBound(LookupRng) - 1
        If LookupRng(i) = LookupVal Then
            j = i + 1
        End If
    Next i
    MatchNonContig = Choose(j, Range("A19"), Range("A30"), Range("A35"), Range("A36"), Range("H15"), Range("H22"))
End Function

Syntax would be:

=MatchNonContig(MAX(F19,F30,F35,F36,K15,K22),F19,F30,F35,F36,K15,K22)

 

[kweaver]

GROAN...I found a problem with the UDF...checking it again.

 

[kweaver]

Think I found the issue:

Function MatchNonContig(LookupVal As Variant, ParamArray LookupRng() As Variant)
    Dim i As Long, j As Long
    j = 0
    For i = 0 To UBound(LookupRng)
        If LookupRng(i) = LookupVal Then
          j = i + 1
        End If
    Next i
          MatchNonContig = Choose(j, Range("A19"), Range("A30"), Range("A35"), Range("A36"), Range("H15"), Range("H22"))
    
End Function