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_type`

and `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

Download Free Example File

**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.

Download Free Example File

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 WorksheetSet ws = ThisWorkbook.Sheets("Sheet1")

' Define the criteria you are searching forDim criteria As Stringcriteria = "example"

' Define the column you are searching inDim searchColumn As StringsearchColumn = "A"

' Loop through each row in the search columnFor i = 1 To ws.Cells(ws.Rows.Count, searchColumn).End(xlUp).Row' Check if the cell in the current row matches the criteriaIf ws.Cells(i, searchColumn).Value = criteria Then' If it does, return the row numberMsgBox "Matching cell found in row: " & iExit ForEnd IfNext 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.

## FAQs

### How do you return a value if two cells match? ›

To compare two ranges cell-by-cell and return the logical value TRUE if all the cells in the corresponding positions match, supply the equally sized ranges to the logical test of the AND function: **AND(range A = range B)** That's how to use the If match formula in Excel.

**How do you return a cell based on row number? ›**

**=INDEX()** returns the value of a cell in a table based on the column and row number. =MATCH() returns the position of a cell in a row or column. Combined, the two formulas can look up and return the value of a cell in a table based on vertical and horizontal criteria.

**How do you return row number in Excel for match? ›**

Supposing you want to know the row number of “ink” and you already know it locates at column A, you can use this formula of **=MATCH("ink",A:A,0)** in a blank cell to get it's row number. After entering the formula, and then press the Enter key, it will show the row number of the cell which contains "ink".

**How do you return a row number of an array in Excel? ›**

**If you use the reference of a range of cells within ROW function, it will return an array of all the row numbers**. For example, in the above example, =ROW(A4:C8) returns an array – {4;5;6;7;8} – as all these rows are covered by the range used within the ROW function.

**How to match two columns in Excel and return a value from another column? ›**

In the Formula Type drop down list, please select Lookup option; Then, select Look for a value in list option in the Choose a formula list box; And then, in the Arguments input text boxes, select the data range, criteria cell and column you want to return matched value from separately.

**How to return value in another cell if a cell contains certain value in Excel? ›**

If cell contains specific text, then return a value

Select the output cell, and use the following formula: **=IF(cell="text", value_to_return, "")**. For our example, the cell we want to check is A2, the text we're looking for is “example”, and the return value will be Yes.

**Which function returns the number of rows that match a specified? ›**

The **COUNT()** function returns the number of rows that matches a specified criterion.

**What is the function to return the current row number in Excel? ›**

Get a Cell's Row Number

If you put a cell reference within this function, it will return the row number for that cell reference. This example would return 1 since cell A1 is in row 1. If it was **=ROW(C24)** the function would return the number 24 because cell C24 is in row 24.

**What is the return match formula in Excel? ›**

The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range. For example, if the range A1:A3 contains the values 5, 25, and 38, then the formula **=MATCH(25,A1:A3,0)** returns the number 2, because 25 is the second item in the range.

**What is the row array function in Excel? ›**

In other words, an array formula in Excel **evaluates all individual values in an array and performs multiple calculations on one or several items according to the conditions expressed in the formula**. Not only can an array formula deal with several values simultaneously, it can also return several values at a time.

### How do I return a cell value every nth row in Excel? ›

**To copy values from every 5th row, starting with the first row in our data, we follow these steps:**

- Select cell E3.
- Enter the formula: =OFFSET($C$3,(ROW(C1)-1)*5,0)
- Press ENTER.
- Copy and paste the formula to the succeeding cells E4 and E5.
- Select cell E9.
- Enter the formula: =OFFSET($C$3,(ROW(C1)*5-1),0)
- Press ENTER.

**What is a return array in Excel? ›**

You can think of an array as **a row or column of values, or a combination of rows and columns of values**. Array formulas can return either multiple results, or a single result.

**How do you return yes if two cells match in Excel? ›**

**How to Test if Two Values Are Equal With the IF Function**

- Select the first cell in the column where you want to return the test results. This will be cell C2 for this example.
- In the formula bar, enter the formula below: =IF(A2=B2, "Yes", "No")
- Press Enter. ...
- Grab the fill handle and drop it on the cells below.

**How do you check if two cells match text in Excel? ›**

Comparing strings in Excel can be done with a simple function. The **=EXACT(A1,B1) function will return TRUE if the two cells contain the same text string**. This is a case-sensitive comparison, so "apple" and "Apple" would not be considered equal.