Lookup matched value or next in sequence

[GAJITCS]

I had this sorted yesterday, but too much clicking of the Undo and lost it.

I have two numbers in cells F5 and F6 and need to match the higher value to a list, displaying either the match or if not matched, the next value in sequence.


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]0.375[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]0.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]0.25[/TD]
[TD]0.50[/TD]
[TD]1.00[/TD]
[TD]2.00[/TD]
[TD]3.00[/TD]
[/TR]
</tbody>[/TABLE]

In this case, searching for the result of max(F5:F6) should return a value of 0.50 from range F8:J8.


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]0.625[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]0.50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]0.25[/TD]
[TD]0.50[/TD]
[TD]1.00[/TD]
[TD]2.00[/TD]
[TD]3.00[/TD]
[/TR]
</tbody>[/TABLE]

But in this case, searching for the result of max(F5:F6) should return the value of 1.00 from range F8:J8.

Should the result of max(F5:F6) be greater than J8, I need to trap it as an error.

Yesterdays solution was so simple.

Many thanks.

 

[Aladin Akyurek]

Try:

=IF(MAX(F5:F6)>MAX(F8:J8),"error",LOOKUP(MAX(F5:F6),F8:J8))

 

[Peter_SSs]

Try

=AGGREGATE(15,6,F8:J8/(F8:J8>=MAX(F5:F6)),1)

 

[GAJITCS]

Thank you both.

Aladin, I couldn't get yours to return the correct value when F5 had a value of 0.625. It still returned 0.5 and not 1.00

Peter, Your's works well and is now in use, but I don't understand it. If you have the time, could you explain it please.

 

[Peter_SSs]

Peter, Your's works well and is now in use, but I don't understand it. If you have the time, could you explain it please.

Good news (the first part).

In English, the formula finds the smallest number (=AGGREGATE(15, ...) in F8:J8 that is greater than or equal to the maximum of F5:F6. Given that F8:J8 are in ascending order that finds either the exact match or the next number in the sequence - which was your stated requirement.

You can get some more information & examples regarding the AGGREGATE function here.

 

[Aladin Akyurek]

Thank you both.

Aladin, I couldn't get yours to return the correct value when F5 had a value of 0.625. It still returned 0.5 and not 1.00

Peter, Your's works well and is now in use, but I don't understand it. If you have the time, could you explain it please.

The range F8:J8 is sorted in ascending order, so I assumed LOOKUP tout court is exactly what is needed. It appears an additional step is needed.


In F7 enter:

=MAX(F5:F6)

Now run:

=IF(F7>J8,"error",INDEX(F8:J8,MATCH(F7,F8:J8,1)+(LOOKUP(F7,F8:J8)) < F7)))

This doesn't involve any array-processing.

 

[Peter_SSs]

=IF(F7>J8,"error",INDEX(F8:J8,MATCH(F7,F8:J8,1)+(LOOKUP(F7,F8:J8)) < F7)))

This doesn't involve any array-processing.

The formula is not valid
When corrected, it is not robust against small values in F5:F6
There is no problem with array-processing here.

 

[Aladin Akyurek]

The formula is not valid

A misplaced paren. A great hit.

When corrected, it is not robust against small values in F5:F6

I don't see any specification involving F7 < MIN(F5:F6), so an Excel error value would not be inadequate.

There is no problem with array-processing here.

And again, the MATCH+KOOKUP formula does not involve array-processing.

Edit for the misplaced paren:

=IF(F7>J8,"error",INDEX(F8:J8,MATCH(F7,F8:J8,1)+(LOOKUP(F7,F8:J8) < F7)))