Find value among 2 columns greater than and less than cell

[westend9876]

I need to find the lowest value in columns C and E that is equal to or greater than the value in column H and return that result in column I. For example the value in H1 is 1.52673. So in cell I1 the return value would be 1.56735 because this is the lowest value in columns C and E greater than or equal to H1.

Col C........Col E.......Col H
1.62711...1.61122...1.52673
1.60505...1.58807...1.57381
1.58776...1.56735...1.62673

 

[bosco_yip]

Try..........

In I1, enter array formula and copy down (confirm enter with pressing CTRL+SHIFT+ENTER 3 keystrokes) :

=MIN(IF(CHOOSE({1,2},C$1:C$3,E$1:E$3)>=H1,CHOOSE({1,2},C$1:C$3,E$1:E$3)))

Regards
Bosco

 

[westend9876]

Try..........

In I1, enter array formula and copy down (confirm enter with pressing CTRL+SHIFT+ENTER 3 keystrokes) :

=MIN(IF(CHOOSE({1,2},C$1:C$3,E$1:E$3)>=H1,CHOOSE({1,2},C$1:C$3,E$1:E$3)))

Regards
Bosco

Is there anyway to do this without an array?

 

[bosco_yip]

Is there anyway to do this without an array?

Try this non-array formula,

=AGGREGATE(15,6,CHOOSE({1,2},C$1:C$3,E$1:E$3)/(CHOOSE({1,2},C$1:C$3,E$1:E$3)>=H1),1)

Regards

 

[bines53]

Hi Bosco,

Getting to use the function AGGREGATE, do not forget you came into my territory.

It is also possible that,
=AGGREGATE(15,6,$C$1:$E$3/($C$1:$E$3>=H1),1)

David

 

[bosco_yip]

Hi Bosco,

Getting to use the function AGGREGATE, do not forget you came into my territory.

It is also possible that,
=AGGREGATE(15,6,$C$1:$E$3/($C$1:$E$3>=H1),1)

David

Thanks David,

I upgraded my Excel version and able to use the AGGREGATE function.

Regards
Bosco