Examples of ROW And ROWS
Let’s see some common examples of using the functions simultaneously to get a better understanding.
We will use the INDEX function and the MATCH function for the following formula. And HP fans are simply going to love this example. 😀
Our data set looks something like this:
And the formula we are to apply looks like this:
Yes, we know – that’s terribly confusing. We didn’t understand it the first time, either. So we’ll take you through all the prospects of this formula one by one once we see what it does.
In this dataset, we wanted to know the row number of an O.W.L Eleven. So we applied the formula and hit enter. The result was this:
Let’s break down the formula.
MATCH(E6,B1:B8,0) – the MATCH function returns the cell’s relative position in the range B1:B8, which, in this case, is Eleven.
INDEX(A1:A8, MATCH(E6, B1:B8,0)) – the INDEX function will use the matched value to return the reference from within the given range.
= ROW (INDEX(A1:A8, MATCH(E6,B1:B8,0))) – the ROW function returns the row number from multiple references.
Note that ROW and INDEX both work together to return the reference. Try using the INDEX function separately. You will see Excel returns the numeric value of that named range instead of showing the row number.
Now let’s see an example with the ROWS function.
We will use the same data set. But this time, we want a random result instead of a particular row number.
For this, we will use the INDEX function and the RANDBETWEEN function.
The formula looks something like this:
Upon applying it, the Excel spreadsheet returns a random result from the vertical array.
See? That’s how easy it is to use the ROW and ROWS functions. 😀