Let’s take a look at how you might put these functions to work.
First, we’ll use ROW to get the row of a particular cell in a list.
Click into a new cell, and type the following formula:
=ROW(INDEX(Apps, MATCH(“Asoka”, Apps, 0)))
In the center, we have MATCH(“Asoka”, Apps, 0).
This returns the relative position of the cell containing the word “Asoka” in the range “Apps” (which is A2:A51).
Outside of this, we have the INDEX function. This takes the number returned by MATCH and returns a cell reference. That’s important. If you run INDEX without ROW, you’ll see the value of the cell returned. But it actually returns the reference, which we can use with ROW.
Finally, ROW takes the cell reference returned as gives us the row number.
Hit Enter to see the result, then we’ll talk about why this is a useful formula:
As you can see, the formula returned 7—which is the row containing the app named “Asoka.”
To see why this is useful, try running MATCH(“Asoka”, Apps, 0).
It returns a 6, which is not the row number.
Because MATCH returns relative positions, it looks only at the named range “Apps.” “Asoka” is in the sixth cell down in the range.
That’s not much of a difference in this case, but if your range is a few dozen cells down from the top of your spreadsheet, you can get more absolute information with the ROW function.
Excel can generate random numbers, but it isn’t quite as easy to get a random cell from a range. We can use ROWS to get a random cell with ease, though.
Here’s the formula we’ll use:
=INDEX(Apps, RANDBETWEEN(1, ROWS(Apps)))
INDEX will give us a cell from the named range “Apps”—but we want it to return a random one. So for the row number, we’ll use a RANDBETWEEN function:
That formula returns a random number between one and the number of rows in the Apps range. That random number is then given to INDEX:
Combining ROW and ROWS with other functions is where you’ll really see their power.
One of the common places you’ll see these functions used is in returning multiple matches for a single search. It’s a rather complex operation, but very useful. We recommend checking out this video from Doug H to see how it works: