Nearest Match Greater Than or Equal to Search Key in Vlookup in Google Sheets

In this article, I’m going to share a simple but powerful hack in Google Sheets using the Vlookup function. We will learn how to return the nearest match greater than or equal to the search key. This trick can be a game-changer as it allows you to replace nested IF statements with the Vlookup function in certain situations.

Understanding the Default Behavior of Vlookup in a Sorted Range

By default, the Vlookup function in Google Sheets behaves as follows: In a sorted range, it returns the nearest match less than or equal to the search key. You don’t need to specify any additional arguments for this behavior.

Let’s take a look at the example below to understand how the vertical lookup function works in a sorted range.

In the above example, the formula in cell G3 is =VLOOKUP(F3,B3:C7,2). Here’s a breakdown of the formula:

• search_key: 25 (F3)
• range: B3:C7
• index: 2
• is_sorted: omitted

The search key is 25, which is located in cell F3. Since it is not available in the first column of the Vlookup range, the formula searches for the nearest match less than or equal to the search key, which is 24. The index column in the formula is 2, so it returns the number 4 from column 2 of the range.

But what if we want the formula to perform the nearest match greater than or equal to the search key? That’s where our hack comes into play.

The Benefit of Forcing Nearest Match Greater Than or Equal to the Search Key in Vlookup

There are specific scenarios where you want to find the nearest match greater than or equal to the search key. Usually, we use logical tests in such cases. Let me explain further.

Let’s consider the first column in the previous example as an age group range, as shown below:

As per the values in column D, the age group ranges are defined. For example, the value 17 in cell A3 represents the age group from 1 to 17.

If we use the default Vlookup formula from the previous example, it won’t give us the desired result. In cell F3, I have entered 25 as the search key, indicating the age. In this case, we want the formula to return the value from the age group range 24 to 32, not from 17 to 24.

This is where forcing the nearest match greater than or equal to the search key in Vlookup becomes valuable. It allows us to get the result we want without using nested IF statements.

The Nearest Match Greater Than or Equal to the Search Key in Vlookup

The Vlookup function is programmed to work in a sorted range. If the search key is not available in the search column, it returns the nearest match less than the search key in the search column. We can’t change this behavior directly.

However, we can modify our range (table) slightly to get the desired result. Here’s how you can do it:

1. Virtually move the first column in the range one row down.
2. Ensure the row size of both columns in the range remains the same.
3. Adjust the number of rows in the second column to match the first column.

To achieve this, we use a modified (virtual) range as shown below:

Actual Range: B3:C7
Modified (Virtual) Range: {{1;B3:B7},{C3:C7;if(,,)}}

In the modified range, we inserted a new cell with the number 1 in the first column to move the range one row down. The second column retains its position. The if(,,) function adds a blank cell at the bottom of the second column to match the number of rows with the first column.

Now we can use the Vlookup formula as before:

=ifna(Vlookup(F3,{{1;B3:B7},{C3:C7;if(,,)}},2))

We also added the IFNA function to return a blank in case of the #N/A error.

Vlookup or Nested IF?

Now, let’s address the question: which formula is more flexible, Vlookup or nested IF?

If you have fewer logical expressions (e.g., age groups), using nested IF or IFS might be more suitable. In such cases, you may not need to maintain a table like in Vlookup.

However, if you have several logical expressions, using nested IF statements can lead to syntax errors and make formula editing cumbersome. In these scenarios, opting for Vlookup is a better choice.

One of the advantages of Vlookup is its flexibility. Unlike the nested IF formula, Vlookup allows us to change the index number to return values from any column in the table.

That’s all about forcing the nearest match greater than or equal to the search key in Vlookup in Google Sheets. I hope you find this hack useful and enjoy using it in your own spreadsheets.

Thanks for reading, and remember to visit Crawlan.com for more insightful Google Sheets tips and tricks!