Search code examples
excelmaxmaxifs

Max value if not this in excel

With the following data set, I want to find the max and min value across rows, and then report the corresponding letter associated with the value. So if the max value is 0.19 and the associated letter is U, then I would like it to populate in a separate column 0.19 and U. There are some that are blank (no letters), so if it is the max/min value and the column next to it is blank, then I would like it to say "blank".

1 U 1 U
0.19 U 0.19 U
0.19 U 0.19 U
2.3 U 2.1 U
7700 200 U
1.4 1 U
10 U 10 U
6.7 0.3 U
380 18
22000 160
8700 59
25 10 U
110 96
0.3 0.24 J+
0.05 U 0.05 R
260 10 U
200 10 U
0.5 U 0.5 U
Solution

  • I guess You want these expected results

    enter image description here

    I Edited the Answer:

    =LET(dt,CHOOSECOLS(WRAPROWS(TRANSPOSE(A2:D2),2),1),ltrs,CHOOSECOLS(WRAPROWS(TRANSPOSE(A2:D2),2),2),mx,MAX(dt),mn,MIN(dt),mxl,INDEX(ltrs,MATCH(mx,dt,0)),mnl,INDEX(ltrs,MATCH(mn,dt,0)),HSTACK(mx,IF(mxl="","Blank",mxl),mn,IF(mnl="","Blank",mnl)))

    I hope this will give you an idea. You have to drag it. or Otherwise if you can use recursive lambda, it will give you the omitted array in one go instead of dragging the formula for thousand rows.