0

So, like I said I have the following formula in excel:

=VLOOKUP($A$8,'\Volumes\remax\BDM\Rapoarte : Analize\2023\[Date 2023.xlsx]GCI'!$A:$M,COLUMN(B2),0)

so if I drag it down it will return:

=VLOOKUP($A$8,'\Volumes\remax\BDM\Rapoarte : Analize\2023\[Date 2023.xlsx]GCI'!$A:$M,COLUMN(B2),0)
=VLOOKUP($A$8,'\Volumes\remax\BDM\Rapoarte : Analize\2023\[Date 2023.xlsx]GCI'!$A:$M,COLUMN(B3),0)
=VLOOKUP($A$8,'\Volumes\remax\BDM\Rapoarte : Analize\2023\[Date 2023.xlsx]GCI'!$A:$M,COLUMN(B4),0)

and I need it to return:

=VLOOKUP($A$8,'\Volumes\remax\BDM\Rapoarte : Analize\2023\[Date 2023.xlsx]GCI'!$A:$M,COLUMN(B2),0)
=VLOOKUP($A$8,'\Volumes\remax\BDM\Rapoarte : Analize\2023\[Date 2023.xlsx]GCI'!$A:$M,COLUMN(C2),0)
=VLOOKUP($A$8,'\Volumes\remax\BDM\Rapoarte : Analize\2023\[Date 2023.xlsx]GCI'!$A:$M,COLUMN(D2),0)

I tried using math formulas for example:

=VLOOKUP($A$8,'\Volumes\remax\BDM\Rapoarte : Analize\2023\[Date 2023.xlsx]GCI'!$A:$M,COLUMN(M2)-COLUMN(B2)+1,0)

And basically everything else that I know

BigBen
  • 46,229
  • 7
  • 24
  • 40
davidpatron
  • 1
  • 1
    Change `COLUMN(B2)` to `ROW(B2)` they both will return 2 to start but as it is dragged down the row will change to 3 then 4 and so on. – Scott Craner Feb 07 '23 at 19:50
  • Check out: https://stackoverflow.com/q/13348825/4961700 – Solar Mike Feb 07 '23 at 20:13
  • ROW(B2) when i drag it down it will change to ROW(B3) B4 B5 etc – davidpatron Feb 07 '23 at 20:31
  • 2
    `ROW(B3)` returns the same thing as `COLUMN(C2)`? – BigBen Feb 07 '23 at 21:23
  • yes, the thing is it will not update the reference – davidpatron Feb 08 '23 at 07:23
  • COLUMN(B2) is 2. ROW(B2) is also equal 2. If you copy/fill down, the next formula will be ROW(B3), which is 3. Which is what you want--you want the VLOOKUP `COLUMN` indicator to go up by +1 on each subsequent row. And the `ROW` value does exactly that--it goes up by 1 each row you copy it down. So you are using the ROW function to create a increment of +1 per row, and then using that as the column index. No matter what row you are starting in, you want to start with a ROW argument that equals your first column. So if your first lookup is column 2, then start with ROW(A2) and copy down. – Max R Feb 09 '23 at 05:19
  • Woww thanks a lot @MaxR, I was using the incorrect function all this time.Thanks mate I appreciate your help. – davidpatron Feb 09 '23 at 09:14
  • @davidpatron It's counterintuitive. "why would I use ROW() when I want to increment a column number?" Because the row number happens to increment by 1 as I fill down. "But I feel like I am 'lying' to Excel by exploiting the ROW() function to get its fill-down behavior, but then sneakily cheating the system by using that number to actually be my vlookup *column* counter." Correct, that's *exactly* what we're doing. – Max R Feb 09 '23 at 16:28

0 Answers0