Return second value in index rather than max value

user_17741343 0
Viewed 39

I'm trying to edit this formula so it returns the second occurrence or match value in the data I'm indexing rather than the MAX value. Can someone please help me understand what exactly needs to be changed? I've tried LARGE and SMALL functions but they're not going to work it seems.

=MAXIFS(
    INDEX('Analyze Run Data'!$A:$ZZ,,MATCH("VPRB"&$Z$2,'Analyze Run Data'!$1:$1,0)),
    INDEX('Analyze Run Data'!$A:$ZZ,,MATCH("SEG",'Analyze Run Data'!$1:$1,0)),
    $A4)
excel-formula excel
1 Answers

Use AGGREGATE:

=AGGREGATE(14,7,
    INDEX('Analyze Run Data'!$A:$ZZ,,MATCH("VPRB"&$Z$2,'Analyze Run Data'!$1:$1,0))/
    (INDEX('Analyze Run Data'!$A:$ZZ,,MATCH("SEG",'Analyze Run Data'!$1:$1,0))=$A4)
    ,2)

The 2 on the end says to return the second highest.

Now if one has Office 365:

=LET(rng,INDEX('Analyze Run Data'!$A:$ZZ,,MATCH("VPRB"&$Z$2,'Analyze Run Data'!$1:$1,0)),
    crit,INDEX('Analyze Run Data'!$A:$ZZ,,MATCH("SEG",'Analyze Run Data'!$1:$1,0)),
    INDEX(SORT(FILTER(rng,crit),1,-1),2)

Again the 2 will return the second of the sorted numbers.

user_48515900
Related