Whilst VLOOKUP is great for many tasks, one of its limitations is that when using TRUE as the 4th argument, to indicate a non-exact match, VLOOKUP can only return the largest value that is less than the value being looked up.

In the screenshot below, B3 contains the formula =VLOOKUP(B2,D2:E6,2,TRUE) which calculates the bonus percent.

The Lookup Value in B2 (39,000) doesn’t exist in D2:D6 however, because the 4th argument of the function is TRUE, Excel will return the “largest value that is less than the value being looked up”

The value being looked up is 39,000. The largest value that is less that 39,000 is 20,000. The bonus associated with 20,000 is 5%.

But what if you need the next largest value instead? By that I mean you want to return the bonus percent associated with 40,000 (this is the next largest value after 39,000).

It’s unlikely that you’d want to do that in this scenario (unless the bonus rules change) however there are plenty of other situations where you do need to return “the next largest value”.

VLOOKUP cannot do this and although this can be done easily with the XLOOKUP function, not all users have XLOOKUP as it’s only included in the newer versions of Excel.

In this video I’ll walk you through step-by-step how to use Excel’s INDEX and MATCH functions to find the next largest value, offering a workaround for VLOOKUP’s limitations.

The formulas that I used in the video:

=VLOOKUP(B2,D2:E27,2,TRUE)

=VLOOKUP(C3,F3:G7,2,TRUE)

=INDEX(F3:G7,3,2)

=INDEX(F3:G7,MATCH(C3,F3:F7,1),2)

=INDEX(F3:G7,MATCH(C3,F3:F7,1)+1,2)

You can download a copy of the file I used in the video by clicking through to YouTube. The link for the file is in the YouTube description.