0

I need to drag a Vlookup down and have the Column index number in to increase by 1 every row I drag it down.

So for example: Row 1:

IF(VLOOKUP(A2,'Jan 2024'!$D:$BP,**35**,0)<=0,"IGNORE",VLOOKUP(A2,'Jan 2024'!$D:$BP,**35**,0))

I need row 2 to be:

IF(VLOOKUP(A3,'Jan 2024'!$D:$BP,**36**,0)<=0,"IGNORE",VLOOKUP(A3,'Jan 2024'!$D:$BP,**36**,0))

The bold 35 is what I need to autoincrement just like how the A2 becomes A3 for the lookup value Autoincrements. Any ideas on how I can do this?

Improve this question
2
  • 1
    Try: =IF(VLOOKUP(A2,'Jan 2024'!$D:$BP,ROW(A35),0)<=0,"IGNORE",VLOOKUP(A2,'Jan 2024'!$D:$BP,ROW(A35),0)) in place of A35 you can use ZZ35 so as to avoid confusion on using or referencing the column A
    – Mayukh Bhattacharya
    Commented Jan 4 at 14:38
  • Replace 35 with ROWS($A$1:$A35) and fill down.
    – Ron Rosenfeld
    Commented Jan 5 at 22:08

1 Answer 1

Reset to default
1

As commented above I will be looking at using the ROW() function in Excel, since it returns the row number for a reference. Like for example, ROW(A35) returns 35 as it is the 35th row in the spreadsheet, therefore when its dragged down, it becomes ROW(A36) which represents the 36th row in the spreadsheet. Therefore, as stated above, we can use the following formula:

=IF(VLOOKUP(A2,'Jan 2024'!$D:$BP,ROW(A35),0)<=0,"IGNORE",VLOOKUP(A2,'Jan 2024'!$D:$BP,ROW(A35),0))

Or,

To avoid confusion in using the Column A as it may or may not contain some other data, hence we can use the Column ZZ.

=IF(VLOOKUP(A2,'Jan 2024'!$D:$BP,ROW(ZZ35),0)<=0,"IGNORE",VLOOKUP(A2,'Jan 2024'!$D:$BP,ROW(ZZ35),0))

or If one having access to MS365 or Excel 2021 can use the LET()

=LET(x, VLOOKUP(A2,'Jan 2024'!$D:$BP,ROW(A35),0), IF(x<=0,"IGNORE",x))
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .