How to return the row number of a matched value in Excel?

How to return the row number of a matched value in Excel?
  • Post author:
  • Post last modified:August 29, 2020
  • Reading time:4 min(s) read

There are two parts of the solution for how to return the row number of a matched value. First part is to search and find the position of the match value in the given column. Part two is to get the row number of the matched value.

When you need to search a specific value within a excel column and find out the position of the matching cell you can use excel MATCH function. Excel MATCH function has following parameters.

 = MATCH (lookup_value, lookup_array, [match_type])

lookup_value: we enter the value or the cell reference for the value that we need to search for

lookup_array: We must specify the cell range as the lookup_array that we intend to search for the lookup value

[match_type]: Square parenthesis enclosing the parameter name indicate that this parameter is an optional parameter. So, match_type parameter is optional, and it accepts the values -1,0 and 1.

Let us quickly see what it is means to have these different values for match_type.

1 – ‘less than’ – If you have chosen the number 1 as the match_type parameter, MATCH function returns the position of the largest value less than or equal to the lookup value. To achieve this result, values should be in ascending order within the selected range. If there is no value less than or equal to the given lookup value, MATCH function returns #N/A error, which says the value is not available to the formula or function.

0 – ‘exact match’ – If you have chosen the number 0 as the match_type parameter, MATCH function returns the position of the value equal to the lookup value. If there are more than one occurrence of the given lookup value, MATCH function returns the position of first occurrence of the lookup value.

-1 – ‘Greater than’ – If you have chosen the number -1 (negative one) as the match_type parameter, MATCH function returns the position of the smallest value larger than or equal to the lookup value. To achieve this result, values should be in descending order within the selected range. If there is no value larger than or equal to the given lookup value, MATCH function returns #N/A error, which says the value is not available to the formula or function.

Below example shows you how to use the MATCH function to return the position of a lookup value in exact match criteria. I prepared the worksheet as shown below.

A screenshot of a cell phone  Description automatically generated

I want to find the position of the matched value for lookup value entered in cell D26 from the cell range “A27:A31”.

Enter the following formula within the cell D27.

=MATCH (D26, A27:A31,0)

A screenshot of a cell phone  Description automatically generated

We can see that it returns the relative position of the cell having the matching value for looked up value. In the above example value “6” located as the 4th item within the “A27:A31” cell range.

Using excel MATCH and ROW functions to return the row number of the match value

We can get the relative position of a matched value using the MATCH function. Then if we know the row number first row of the data set, we can find the row number of the match value.

There is a function called “ROW ()” which return the row number for reference cell. You can use that function to get the row number of the first row of the given data set. Then simply add up the relative position of the match value reduced by one to return the row number of the match value.

Following demonstration will help you to clearly understand the procedure for return the row number of match value by using MATCH function and ROW function.

exact match 1

In above figure I have found the relative position of number “6” in the “Values” column as 4.

Then we can use “ROW” function to get the row number of the value “75” which is the first item of the column.

return the row number of the first item using ROW() function

It will return the row number of the value “75” which is row number 27.

Now how do we get the row number of the matched value for the given lookup value. Earlier we got the position of the matched value using MATCH function. That means we know in which row that matched value is located within the selected cell range. Deduct one from the value returned by the MATCH function and add it to the row number of the first item. It will give you the row number of the match value.

row number match value

That will give you the excel sheet row number of the match value as shown in the below figure.

final output

Leave a Reply