Excel min formula in offset formula-CodePudding

I'm trying to get left cell value of min value cell

i tryed this formula. but its not working

=OFFSET(min(AB2,AD2,AF2,AH2,AJ2),0,-1)

asking for correction in this formula

CodePudding user response：

MIN returns a value, not a cell reference, so you can't use it as input of OFFSET.

I'm not aware of any solution with built-in functions which would work on a not-contiguous range.

CodePudding user response：

With Office 365:

=FILTER(HSTACK(AA2,AC2,AE2,AG2,AI2),HSTACK(AB2,AD2,AF2,AH2,AJ2)=MIN(AB2,AD2,AF2,AH2,AJ2))

With older versions:

=IF(AB2=MIN(AB2,AD2,AF2,AH2,AJ2),AA2,"")&
 IF(AD2=MIN(AB2,AD2,AF2,AH2,AJ2),AC2,"")&
 IF(AF2=MIN(AB2,AD2,AF2,AH2,AJ2),AE2,"")&
 IF(AH2=MIN(AB2,AD2,AF2,AH2,AJ2),AG2,"")&
 IF(AJ2=MIN(AB2,AD2,AF2,AH2,AJ2),AI2,"")

CodePudding user response：

For pre-O365 versions of Excel, assuming the entries in the range AB2,AD2,AF2,AH2,AJ2 are non-negative:

=OFFSET(
    INDEX(
        (AB2, AD2, AF2, AH2, AJ2),,,
        MATCH(1, FREQUENCY(0, (AB2, AD2, AF2, AH2, AJ2)), 0)
    ),,-1
)

Note the importance of the parentheses in the part:

(AB2, AD2, AF2, AH2, AJ2)