How to find lowest number using criteria from 2 columns-CodePudding

In my situation I have a data set that contains 2 columns I am interested in querying, along with a target value I need to accommodate. The target value is 593.63 which will be on another worksheet. That number will not have an exact match in column V, so I am using a MIN IF combined function to find the closest number just over that number. the query looks as such

=VALUE(MIN(IF(Table3[Total Target Force]>'Spring Calculator'!K25,Table3[Total Target Force])))

Table3[Total Target Force] = The column in which the value I need to return in another cell resides

'Spring Calculator'! = The worksheet that contains the target value in the cell K25 (593.63)***

My question is how to add a second criteria to this using the min function again. I have a column called "Spring Count" in which there are another set of numbers. I would like to only find the closest Total Target Force where the Spring Count is the lowest number it can be. This formula will be variable, so I can't say something like =MIN[Spring Count], it needs to be tied in with my current formula, finding the closest number to the target, but also considering the lowest number in the "Spring Count" column.

In my ideal situation my goal is to find the closest number to the target in my data set from the total target force column, but only if the number in the spring count column is the lowest in the initial return.

If more information is required I am happy to provide any that is needed. I've been racking my brain for days on this and can't figure it out.

CodePudding user response：

If you have the MINIFS() function, and add a helper column (column E has the formula =C2-$H$1 copied down) then you could use this formula:

=MINIFS(C2:C17,B2:B17,"TRUE",D2:D17,MIN(D2:D17),E2:E17,">=0")

If you don't have MINIFS() then you can use this

CodePudding user response：

You can achieve this without helper columns by using a boolean XLOOKUP formula. Likely the same for INDEX/MATCH, but that seems more complicated.

Cell F2 formula:

    =XLOOKUP(1,($B:$B >= $F$1)*($C:$C = MINIFS($C:$C,$B:$B,">="&$F$1)),$B:$B)

Parts:

($B:$B >= $F$1) --> finding which rows in array meet or exceed your target value

$C:$C = MINIFS($C:$C,$B:$B,">="&$F$1) --> The MINIFS part of this section looks for the smallest number in column C where Column B value meets or exceeds the target value.

),$B:$B) --> Complete the formula and return the value in Column B which validates as true for the other statements in the formula.