Three Ways to get the First Row Number in a Range

This post explains that how to get the first row number in a range in excel. This post will guide how to get the first row number of a range via the below three ways:

Get First Row Number using the ROW Function Only

If the ROW function use a Range as its argument, it only returns the first row number. Actually, it returns an array contain all row numbers as elements, but it just only display one value that it is the first row number. So you can use it to get the first row number in a range directly.

Assuming that you want to get the first row number in range B3:D6, just enter the below formula into the cell F3:

=ROW(B3:D6)

Get First Row Number using the ROW Function Only 1

In Cell F3, the above formula returns 3, it indicated that the first row number is 3 in the range B3:D6.

Get First Row Number using the Row Function within MIN Function

You can also use the ROW function within the MIN function to get the first row number in a range.  Like I said above, the ROW function actually return an array, so you can use MIN function to get the minimum value in the array. For example, to get the first row number of range B3:D6 using the following formula:

=MIN(ROW(B3:D6))

 Get First Row Number using the ROW Function Only 2

Get First Row Number using the Row function within INDEX Function

You can also use the INDEX function to get the reference of the first row in a range, then combined to the ROW function to get the first row number of a range. Just try the following formula:

=ROW(INDEX(B3:D6,1,1))

The INDEX function returns the reference at a given position based on Row_num and Col_num arguments. So the INDEX(B3:D6,1,1) formula will return the reference of the first row in the range B3:D6.

Get First Row Number using the ROW Function Only 3


Related Formulas

  • Combine Text from Two or More Cells into One Cell
    If you want to combine text from multiple cells into one cell and you can use the Ampersand (&) symbol.If you are using the excel 2016, then you can use a new function TEXTJOIN function to combine text from multiple cells…
  • Split Text String to an Array
    If you want to convert a text string into an array that split each character in text as an element, you can use an excel formula to achieve this result. the below will guide you how to use a combination of the MID function, the ROW function, the INDIRECT function and the LEN function to split a string…
  • Find the Relative Position in a Range or Table
    If you want to know the relative row position for all rows in an Excel Range (B3:D6), you can use a excel Array formula as follows:=ROW(B3:D6)- ROW(B3) + 1. You can also use another excel array formula to get the same result as follows:=ROW(B3:D6)-ROW(INDEX(B3:D6,1,1))+1…
  •  Get the Last Row Number in a Range
    If you want to get the last row number in a range, you need to know the first row number and the total rows number of a range, then perform the addition operation, then subtract 1, the last result is the last row number for that range.…

Related Functions

  • Excel INDEX function
    The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
  • Excel ROW function
    The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
  • Excel MIN function
    The Excel MIN function returns the smallest numeric value from the numbers that you provided. Or returns the smallest value in the array.The MIN function is a build-in function in Microsoft Excel and it is categorized as a Statistical Function.The syntax of the MIN function is as below:= MIN(num1,[num2,…numn])….

 

Related Posts

Creating a Table with Automatic Row Numbering
create table with automatic row number1

When creating an Excel Table, you may use a calculation relying on the ROW function to insert row numbers automatically. The formula in A2, which has been copied down, is as follows: =ROW()-ROW(Table1[#Headers]) Note: the default table name is Table1. ...

Get Address of First Cell in Range

We will learn how to get address of first cell in range in this post. At Instance, we must determine the address of first cell in range to use as the data table's value. For the DATA table, we have ...

Abbreviate Names Or Words in Excel
abbreviate names1

As an MS Excel user, you might have come across a task where you need to abbreviate different names or words, and there are also possibilities that you might have done this task manually by assuming that there isn't any ...

Filter or Extract with a Partial Match
filter with partial match1

Assume you have a table consisting of a few cells having few values, and you want to filter out the set of records with the partial match. You might take it easy and would prefer to manually filter out the ...

Extract matching values From Two Lists
extract matching values from two list1

Suppose that you are working with two lists containing few values, and you want to extract the matching values from those two lists into another separate list. You might prefer to manually extract the matching values from the two lists, ...

Extract all Partial Matches
extract all partial matches1

Just assume that you have a range of data that contains a text string and you want to extract the few partial matches into another separate range of cells; then you might think that it's not a big deal; because ...

Extract Multiple Match Values into Separate Columns

If you have a few values/items in the excel sheet and you are thinking that with the aid of the “VlOOKUP” function you can look for a specific value, extract it and then put the matching item into the separate ...

Average the Last N Numeric Values in Excel

AVERAGE function is one of the most popular functions in Excel. Apply AVERAGE together with some other functions, we can calculate average simply for some complex situations. In this article, we will introduce you to calculate average of the last ...

How To Sum the Largest N Values in Excel

Sometimes we may want to sum the largest N numbers or top N numbers in a range. In this article, we will show you the method of “SUM the Largest N Numbers” by a simple formula which consist of SUMPRODUCT ...

How to Sum the Smallest N Values in Excel

Sometimes we may want to sum the first smallest N numbers in a range in Excel. In this article, we will show you the method of “SUM the Smallest N Numbers” by a simple formula which consist of SUMPRODUCT and ...

Sidebar