SMALL IF OR basic question

[svendiamond]

=SMALL(IF(X_LANE=D2,X_TIME),1)

CTRL+SHIFT+ENTER

Is working fine for me, returning the smallest value in my array "X_TIME" when "X_LANE" equals D2. However, I want to try this:

=SMALL(IF(OR(X_LANE=D2,X_LANE=C2),X_TIME),1)

But excel is returning the smallest value in my array "X_TIME" regardless of my conditions. Can anyone help me come up with a formula to find the smallest value in an array when either one of my two conditions are true?

 

[XOR LX]

Hi.

You can actually use:

=SMALL(IF(X_LANE=C2:D2,X_TIME),1)

here.

Regards

 

[Aladin Akyurek]

Try...

SMALL(IF(ISNUMBER(MATCH(X_LANE,C2:D2,0)),X_TIME),1)

or

MIN(IF(ISNUMBER(MATCH(X_LANE,C2:D2,0)),X_TIME)

 

[svendiamond]

Hi.

You can actually use:

=SMALL(IF(X_LANE=C2:D2,X_TIME),1)

here.

Regards

Thank you!

 

[Aladin Akyurek]

@svendiamond

The best approach to OR in array-form is IsNumber\Match, not an equality test...

 

[XOR LX]

The best approach to OR in array-form is IsNumber\Match, not an equality test...

Could you kindly justify the reasons for your statement for the benefit of all those who may come across this post?

In general, you may be correct, though that does not mean that alternative set-ups should not also be considered. On the contrary, some of these may actually prove to be "better" in certain situations, one of which perhaps being that discussed here.

Regards