ROW Function in HLOOKUP function

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