Autofill the Row_Index_num field on a Hlookup

Occasional Contributor

i am currently creating a table auto populated with Hlookups. the table as formulas looks a bit like this



A =hlookup(A1,'Sheet2'A1:A2,[2],False)

B =hlookup(A1,'Sheet2'A1:A2,[3],False)


When i try to use the drag feature to autofill rows below it doesnt realize i want the bracketed number ([2] & [3]) to go up by one for each row and therefore i have been manually changing the number to fit the line it is on. I have to do this for 999 lines however and was wondering if anyone knew of way to get this to autofill


Thanks in advanced

5 Replies

Instead of constants (2 & 3) you may use formula like ROW()+1 or any other one which changes the value when you drag cells.

as in replace the hlookup with a row function or replace the constant within the hlookup with a row function? I tried plugging in a row function as the constant in the hlookup and it did not work, and I do not think I will be able to run the table as i need to without the hlookup.

Thanks for taking the time to respond and help!

Please check attached, it works with function

Oh I see, i was thinking i had to plug in a starting cell in the () in Row()+1. I had a small problem with the starting result being 2 rows below where my data starts, but was able to simply shift my table down to make it work.

Thanks again for the help I got it working how i need now!

You may use ROW()+something, e.g. ROW()+ROW($A$1) if A1 is your header cell, or like 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies