?
Solved

HLOOKUP in Excel- increment row_index_num

Posted on 2011-03-17
6
Medium Priority
?
1,718 Views
Last Modified: 2012-05-11
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
Comment
Question by:KevinHatt
6 Comments
 
LVL 33

Accepted Solution

by:
jppinto earned 2000 total points
ID: 35156747
Try like this:

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

jppinto
0
 
LVL 33

Expert Comment

by:jppinto
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 35

Expert Comment

by:Rob Henson
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
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
LVL 85

Expert Comment

by:Rory Archibald
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

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

Expert Comment

by:jppinto
ID: 35157104
Glad I could help!

Thanks for the grade and the kind words.

jppinto
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question