Solved

# HLOOKUP in Excel- increment row_index_num

Posted on 2011-03-17
1,204 Views
Hi Experts,

I have a HLOOKUP formula:
=HLOOKUP(QR5,\$B\$3:\$QO\$5000,2) where 2 is the row_index_num

I need to be able to increment that row index num by 1 each when the formula is copied to the next row. I tried using a simple counter and referencing that row_index_num to the cell containing the counter, but returned an error message (#VALUE)

Any help appreciated...
Kevin
0
Question by:KevinHatt

LVL 33

Accepted Solution

jppinto earned 500 total points
ID: 35156747
Try like this:

=HLOOKUP(QR5,\$B\$3:\$QO\$5000,ROW(A2))

jppinto
0

LVL 33

Expert Comment

ID: 35156847
ROW(A2) will give the row number 2. When you copy this formula to the next row, it will change to ROW(A3) thus giving you the row number of 3, and so on...
0

LVL 31

Expert Comment

ID: 35156855
You can also use the ROW function with no reference which will return the row on which the formula is being used, then plus or minus a number to adjust if necessary.

For example if the above formula from jppinto was on row 2 there would be no need for an adjustment but if it is on row 5 it would be:

=HLOOKUP(QR5,\$B\$3:\$QO\$5000,ROW()-3)

Cheers
Rob h

0

LVL 85

Expert Comment

ID: 35156984
Is the formula in the next row supposed to be:
=HLOOKUP(QR5,\$B\$3:\$QO\$5000,3)
or:
=HLOOKUP(QR6,\$B\$3:\$QO\$5000,3)
?

0

Author Closing Comment

ID: 35157019
Hi jppinto...thanks for the prompt help
Full points and gratitude :-)
0

LVL 33

Expert Comment

ID: 35157104

Thanks for the grade and the kind words.

jppinto
0

## Featured Post

### Suggested Solutions

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity anâ€¦
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calculâ€¦
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.