How to return row number of a matching cell

In this tutorial I will explain following four topics, covering various aspects on how to return the row number in Microsoft Excel.

1. Excel functions to get row number
2. Excel get row number with a matching value
3. Excel return row number based on criteria
4. Excel VBA find row number of matching value

1. Excel functions to get row number

Use ROW() function or CELL() function to return the row number of a given cell in Excel. These two functions accept slightly different parameters as function arguments. But both can be used to achieve similar results.

Below you can see how to ROW and CELL functions to return the row number.

ROW () function

The ROW function accepts just a single parameter which is the cell reference of the cell that we need to get the ROW number.

= ROW(reference)

And it returns the corresponding row number based on the given cell reference.

If you would not set the reference for the ROW function, then the ROW() function returns the row number of the cell where you had typed the function.

For example, "=ROW(B12)" returns 12 which is the relevant row number for cell B12.

CELL () function

The cell function accepts two function parameters which are info_typeand reference.

= CELL(info_type, [reference] )

Technically, the "reference" parameter is optional. But it does not seem to work correctly if there are other instances where you have used the ROW() function within the given Excel Sheet. So it is best to input both of the parameters.

So, how to use the CELL() function to get the row number in Excel?

When you type in the opening parenthesis of the CELL function within an Excel cell, it drops a list of info_types that is possible to insert as the first parameter. You would notice that all of them are placed within double quotation marks to indicate that it is a text type input.

One of them is "row" and that is one we need to use as the first parameter (info_type) of the CELL() function.

Then put a comma (,) and select or input the cell address that you need to get the row number.

For example, ""=CELL("row",B12)" returns 12 which the relevant row number for the cell B12.

2. Excel get row number with a matching value

There are two parts of the solution for how to return the row number of a matched value. The 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 column in an Excel spreadsheet and find out the position of the matching cell you can use the Excel MATCH function. Excel MATCH function has the 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 indicates that this parameter is optional. So, the match_type parameter is optional, and it accepts the values -1,0 and 1.

Let us quickly see what it is meant 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.

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)

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 4^th item within the “A27:A31” cell range.

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

Follow the steps below to return the row number of a matched value in Excel

Step 01

Find the relative position of a matched value using the MATCH () function.

Step 02

Find the row number of the first row of the given data set by using the ROW() function.

Step 03

Deduct 1 from the value returned by the MATCH function and add it to the row number of the first item.

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.

Now You can use ROW() 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.

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.

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.

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

Next week I'll explain parts 3 and 4 of this tutorial. Keep in touch!

3. Excel return row number based on criteria

Using ROW() function get return the row number of the first value of the range in concern. Then use the Excel MATCH ()function to find the relative position of the value that matches with the given criteria. Finally, you can add up the return value of the ROW() function and the Return value of MATCH function to get the row number of the matching cell with given criteria.

Example on how to return the row number based on a criteria

Let's consider a range of cells having list of random numbers. Now let's say that we need to find the row number of the smallest value within that list of numbers.

First we will find the row number of the first value of the given range.

Select cell "F7" and then insert the formula "=ROW(" and select the first cell of the range of cells with numbers list. Then hit 'Enter'. Now you get the row number of the first cell in the given range.

Then we have to use a logical function to find the smallest value in the list of numbers. For that we use excel SMALL() function. It can return the smallest number in the list. Then we need to find the relative position of the smallest number just found.In Cell "F8", insert the formula,

=SMALL(D7:D17,1)

Now, value of the formula in cell "F8" is the smallest value of the range "D7:D17" which is '2'. After that we need to get the relative position of the value that matches with given criteria. For that we can use Excel MATCH() function.

In cell "F9", insert the formula,

=MATCH(F8,D7:D17,0)

This above formula return relative position of the smallest number.

Now as you know the smallest number is located in the 4th place of the given range, you can combine it with row number of the first item to get the row number of the smallest number.

4. Excel VBA find row number of matching value

Here is an example of VBA code that can be used to return the row number of a matching cell with a given criteria:

Sub FindMatchingCell()

Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1")

' Define the criteria you are searching for Dim criteria As String criteria = "example"

' Define the column you are searching in Dim searchColumn As String searchColumn = "A"

' Loop through each row in the search column For i = 1 To ws.Cells(ws.Rows.Count, searchColumn).End(xlUp).Row ' Check if the cell in the current row matches the criteria If ws.Cells(i, searchColumn).Value = criteria Then ' If it does, return the row number MsgBox "Matching cell found in row: " & i Exit For End If Next i

End Sub

This code assumes that the sheet you are searching in is named "Sheet1" and the column you are searching in is "A". You can adjust these values as needed. The code also uses the End(xlUp) function to find the last row in the search column, so it will only loop through the used rows in that column. The code will return the row number of the first cell that matches the criteria and exit the loop.