Excel Finding the Min Value Across Multiple Columns for Selecting A Product Meeting Criteria

Question

I have over 900 different beams and based on some other design values there are certain ones that satisfy the requirements. I am trying to pick the lightest beam by weight from the list of acceptable options. If there are equally weighted beams then the shorter one is easier to transport. I basically want to choose the shortest lightest beam, but this is difficult because many beams have the same weight and the same height.

I have Googled the problem and tried a many formulas, but neither none seem to work correctly. Can anyone assist?

As you can see from below the W610x82 works with the design but the W530x82 is shorter so it’s the better option. Excel though indexes the first 82 it comes across. If I go the other way and choose by depth then the shortest beam is W460x113 but it is not the lightest and some beams are the same height so again its difficult top index them. enter image description here

Answer 1

using SORT/FILTER:

=TAKE(SORT(FILTER(B:D,C:C=MIN(C:C)),3,1),1,1)

Answer 2

Match Lightest Shortest

MS365

=LET(data,B4:D12,b_col,1,wph_col,2,h_col,3,
    w,CHOOSECOLS(data,wph_col)*CHOOSECOLS(data,h_col),
    INDEX(SORT(FILTER(data,w=MIN(w)),h_col),1,b_col))

Excel 2021

=LET(data,B4:D12,b_col,1,wph_col,2,h_col,3,
    w,INDEX(data,,wph_col)*INDEX(data,,h_col),
    INDEX(SORT(FILTER(data,w=MIN(w)),h_col),1,b_col))