In my last article, I discussed about the function COLUMN & COLUMNS and the use of these functions with VLOOKUP, nested.
In this article, I will show you another function ROW which
is also is a useful function in Microsoft Excel if used wisely.
ROW() works same as the COLUMN function, the only difference is it gives you the number of the row where this function is written.
Let us
first describe the function and then we will see how can we use this function
in Excel.
Description
The Microsoft Excel ROW function returns the row number of a
cell reference.
The ROW function is a built-in function in Excel that is
categorized as a Lookup/Reference Function.
Syntax
The syntax for the ROW function in Microsoft Excel is as follow.
=ROW( [reference] )
Parameters or Arguments
Reference
Optional. It is a reference to a cell or range of cells.
Returns
The ROW function returns a numeric value.
 |
| ROW Function without Reference |
It’s a simple function to remember and to use. The very
basic question which may arise in your mind is where to use it. I use ROW
function to make HLOOKUP dynamic. Why HLOOKUP ?
Because HLOOKUP is such a function which looks up for data
row wise, and like VLOOKUP where it asks for COLUMN number, HLOOKUP asks for
ROW number. We must enter the ROW number to get the desired data using HLOOKUP.
The trick is to use ROW function in HLOOKUP’s
row_index_number argument or parameter.
Lets take one example of it.
Suppose we have a table arranged in the following way. We should
get the data row wise using HLOOKUP. The syntax of the HLOOKUP function is as
follows.
=HLOOKUP(lookup_value, table-array, row_index_number,
[range_lookup])
 |
| Row function in Microsoft Excel |
Now instead of writing the row number in row_index_number, if
we write ROW() and if we show this function the reference, it will not be
necessary to add the row number manually. Because the row number now will be supplied by the ROW function itself.
Please note that while entering the function ROW within HLOOKUP function you have to give the cell's address in the ROW function.
=HLOOKUP(E15,E2:H5,ROW(E2),0)
Please note that while entering the function ROW within HLOOKUP function you have to give the cell's address in the ROW function.
=HLOOKUP(E15,E2:H5,ROW(E2),0)
Please refer to the below image. The working of ROW function
is same as the working of COLUMN function.
 |
| ROW function in HLOOKUP function |
Hope you have enjoyed this article.
Thanks for reading...
