In this post, let’s learn how to return the nearest match greater than or equal to the search key in Vlookup in Google Sheets. It’s a simple hack but going to be worth trying because you can sometimes replace nested IF with the Vlookup in this way.

In a sorted range, the default behavior of Vlookup is as follows.

We can use Vlookup to return the nearest match less than or equal to the search key in a sorted data range. For that, just leave using the optional argument, i.e., is_sorted, in the formula.

Please see the below example, which explains how the vertical lookup function works in a sorted range.

Please find the following formula in cell G3.

=VLOOKUP(F3,B3:C7,2)

You may be familiar with this formula; still, let me explain it.

Sytnax: VLOOKUP(search_key, range, index, [is_sorted])

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

The search key is 25, which is 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, i.e., 24.

The Index column in the above formula is 2, so it returns the number 4 from column 2 of the range. So far, so good. But I want the formula to perform the nearest match greater than or equal to the search key in Vlookup.

As per the above example, I want the formula to search for the nearest match, i.e., 32, and return the number 6 from column 2.

What’s the benefit of doing so?

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

There are some specific scenarios where you want to Lookup the nearest match greater than or equal to the search key. Usually, we use logical tests in such cases.

Didn’t get it? Don’t worry. I’ll explain further.

Let’s read the first column in the above table as below in column 3.

As per column D, the value 17 in A3 represents age group 1 to 17, 24 in F4 represents age group 17 to 24, and so on.

If so, the above Vlookup doesn’t serve the purpose. In cell F3, I have put 25 (age) as the search key. So I here want the formula to return value from 24 to 32 age group, not from 17 to 24 age group.

Here comes the purpose of using the nearest match greater than or equal to the search key in Vlookup in Google Sheets. Before going to that formula tips, let’s learn how we can use nested IF here.

Nested IF Way

We can use IF or IFS here. I prefer to use the former as it works better in an array.

Unlike in vertical lookup here, we don’t require the table. Enter the age to search in cell F3. The formula will take care of the rest.

=if(F3<1,,if(F3<17,2,if(F3<24,4,if(F3<32,6,if(F3<41,8,if(F3<57,10,))))))

The above is an alternative to the nearest match greater than or equal to the search key in Vlookup. But it is not flexible as Vlookup. Why?

I will come to that later.

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

The function Vlookup is programmed to work in a sorted range as below.

If the search key is not available in the search column, the nearest match that less than the search key in the search column is considered. We can’t change that.

What we can do is to modify our range (table) slightly. By doing so, we can ensure that the formula returns the result we want.

Solution

Virtually move the first column in the range one row down. We should do that without compromising the row size of both the columns in the range. Here is how.

Actual Range: B3:C7

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

To move B3:B7, the first column in the range, one row down, I have inserted a new cell in the first column of the virtual range that contains the number 1.

The second column retains its position. But since the first column has one additional row now, we must match the total rows in the second column with the first column.

The if(,,) adds a blank cell at the bottom of the second column. So both the columns match in terms of the number of rows.

Now we can use the Vlookup as earlier.

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

Additionally, I have used the IFNA to return blank in case of #N/A error.

Vlookup or Nested IF?

Here comes a genuine question. Which formula is more flexible?

If the logical expressions (here age group) are less than 10, use the nested IF or IFS. Because you may not require to maintain a table like in Vlookup.

But if there are several logical expressions, there are chances for us to make syntax errors. You may feel editing the formula in the future tiresome. In such a scenario, go for Vlookup.

There is one more advantage of the Vlookup. It’s the flexibility of it.

In the nested IF formula, we have predefined the values to return. In Vlookup, we can change the index number to return values from any column in the table.

That’s all about nearest match greater than or equal to the search key in Vlookup in Google Sheets.

Thanks for the stay. Enjoy!