x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 1884

# HLOOKUP in Excel- increment row_index_num

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
KevinHatt
1 Solution

Commented:
Try like this:

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

jppinto
0

Commented:
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

Finance AnalystCommented:
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

Commented:
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 Commented:
Hi jppinto...thanks for the prompt help
Full points and gratitude :-)
0

Commented:

Thanks for the grade and the kind words.

jppinto
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.