VLookup next highest value

[tiredofit]

Assuming I have data like this in cells A1 through to B5:

10 Apple
20 Orange
30 Pear
40 Plum
50 Watermelon

I want a formula so if I enter 10, it returns Apple and if I enter 20 it will return Orange.

That formula would be:

=VLookup(J1,A1:B5,2,False)

where J1 is the value to lookup.

However, I want a variant of this, in that if I enter 15, I want to return Orange.

but the following formula returns Apple:

=VLookup(J1,A1:B5,2,True)

The following taken from here:

https://www.extendoffice.com/documents/excel/2704-excel-vlookup-next-largest-value.html

does indeed work:

=INDEX(B1:B5,MATCH(J1,A1:A5)+(LOOKUP(D5,A1:A5)<>J1))

but if I enter a value less than 10 or greater than 50, I get #N/A and /REF! respectively.

How can I amend the formula for my needs?

Thanks

 

[jasonb75]

but if I enter a value less than 10 or greater than 50

What result do you expect if you enter a value greater than 50? There is no value greater than 50 in the list so in theory the error for that is correct based on the rest of your logic.

=IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($A$2:$A$5)/($A$2:$A$5>=J1),1)),"No Match")

 

[tiredofit]

What result do you expect if you enter a value greater than 50? There is no value greater than 50 in the list so in theory the error for that is correct based on the rest of your logic.

=IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($A$2:$A$5)/($A$2:$A$5>=J1),1)),"No Match")

I understand it's returning the correct result but I wanted to know if it can be amended so that it'll return Watermelon.

My quick fix was to change the 50 in the table to a very large number and add a 0 before 10 and return "no fruit".

 

[jasonb75]

In that case, try

=IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($A$2:$A$5)/($A$2:$A$5>=J1),1)),$B$5)

Base on your original question, anything below 10 should return 10 as the next highest value, which is what this one does. I've amended the last bit so that it returns the last thing in the list (B5) if the value exceeds the max in column A.

 

[tiredofit]

In that case, try

=IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($A$2:$A$5)/($A$2:$A$5>=J1),1)),$B$5)

Base on your original question, anything below 10 should return 10 as the next highest value, which is what this one does. I've amended the last bit so that it returns the last thing in the list (B5) if the value exceeds the max in column A.

Thanks, that's great.