Forum Discussion

ChadBoronow's avatar
ChadBoronow
Copper Contributor
Jan 31, 2019

Autofill the Row_Index_num field on a Hlookup

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

 

   1 

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

excel

6 Replies

Sort By
  • arceenium's avatar
    arceenium
    Copper Contributor
    Jul 21, 2023

    =Row() functions shows what the row of a Cell/range where it belongs be ChadBoronow 

    Example =Row(A1), wil return a value of 1

    when you used a Hlookup the row number changes according to the heading (row_index_num) so you need to match the Row() and the value of row according to the Hlookup

    The solution is very easy you need find out a number need to be subtracted in Row() to match the row_index_num

    Example the
    row_index_num is 2
    Row(A14) is 14

    so you need to subract 12 on  Row(A14) so you will have 2

    hlookup(A1,'Sheet2'A1:A2,Row(A14)-12,true

  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Jan 31, 2019

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

    • ChadBoronow's avatar
      ChadBoronow
      Copper Contributor
      Jan 31, 2019
      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!

Resources