Next largest match with MATCH Function🤔 is basically a way to find your data but with a different approach. Here, a different approach🤓 means, when you have a limit or an approx. number in your mind and you jot the same to get the next largest or in other words (the near around data to it, in your sheet).

Now, let’s take an example to elaborate the same🤗:

MATCH Function

As you can see in the image above, we have product as well as sales of the same. In the right side of the image, you can observe that we have written 55000 in sales.

Previously: How to show formula text with FORMULATEXT Function?

Now let’s say, we have an approx. value or we are looking for a product whose sales is around 55000. To find the same, we will use the MATCH Function.

MATCH Function

To begin with the same, we will input the MATCH Function i.e. as follows:


The following formula requires lookup value, lookup array, and match type.

MATCH Function

In the formula, the lookup value will be the 55000 sales value that we already have in mind. So, we will select the same i.e. cell G3.

MATCH Function

Then we will come to the point of lookup array. Lookup array is the range from which we need to get our findings. So, as we are looking for the product which has the next largest match to 55000 in sales, our lookup array will be the sales range.

MATCH Function

Then comes the match type. In this, there are three options available in formula:

(…)1 – Less than

(…)0 – Exact match

(…)-1 – Greater than

You need to be careful in this case, because less than and greater than option can be selected only if the data is in either ascending order or descending order.

And the option 0 is chosen when you are sure that the number in your mind is the exact value you are looking for.

As the sales range is in descending order, so we will be selecting the option (…)-1 – Greater than.

MATCH Function

Now, all we have to do is close the function, by this our formula becomes as follows:


MATCH Function

As you press enter, the answer pops on the screen. The answer is 4, i.e. 4th position in the sales range, i.e. 61000.

This means that the next largest value (by holding 55000 in hand) is 61000.

MATCH Function

By this, if the position is 4th, this means that the product no. 204 with sales 61000 is the next largest match to 55000.

MATCH Function

It’s easy, isn’t it😌?