Dragging Vlookup across columns: Have col_in_# increase by 1 automatically?

[JCarollo765]

This is the function I am working with: =IFERROR(VLOOKUP($A$35,'P&L R&D'!$F$5:$H$58,3,FALSE),0) and I want the column index number to increase by 1 each time it is dragged across columns.

Thanks!

[BadlySpelledBuoy]

Please Login or Register  to view this content.

BSB

[Jonmo1]

Try Index Match instead

=IFERROR(INDEX('P&L R&D'!H$5:H$58,MATCH($A$35,'P&L R&D'!$F$5:$F$58,0)),"")

[Tony Valko]

Your table array only contains 3 columns: 'P&L R&D'!$F$5:$H$58

Why would you want to increase the column index number beyond 3? That would generate an error.

The best method for incrementing is to use the cell address of the first cell the formula is entered into.

If copying across a row and the first formula is entered into cell B2:

COLUMNS($B2:B2)

As you drag across the row the COLUMNS function increments as such:

COLUMNS($B2:B2) = 1
COLUMNS($B2:C2) = 2
COLUMNS($B2:D2) = 3
COLUMNS($B2:E2) = 4
etc
etc

If copying down a column and the first formula is entered into cell B2:

ROWS(B$2:B2)

As you drag down the column the ROWS function increments as such:

ROWS(B$2:B2) = 1
ROWS(B$2:B3) = 2
ROWS(B$2:B4) = 3
ROWS(B$2:B5) = 4
etc
etc