Vlookup Help (Need to replace col_index_number by +1) Easy way to do that?

[JCarollo765]

Here is the formula I am working with:

=IFERROR(VLOOKUP($A10,'P&L MedSci'!$E$5:$T$85,9,FALSE),0)

I want to be able to make that 9 a 10 to reference the next column over. Is there an easy way to replace all the 9's to 10's?

Or a formula so when I drag it over it will only change the col index number?

Thanks for any help!!

[MrShorty]

Is there an easy way to replace all the 9's to 10's?

Would you consider a Find/Replace?
I have frequently put references or formulas in that argument. If I have several columns to return, I will put the desired column number at the top of my output table, then refer to that in the formula:

Please Login or Register  to view this content.

I can use formulas for that top row, if I prefer a formula to hand entered values.

I have even included this kind of formula inside of the VLOOKUP() function -- usually involving the COLUMN() or COLUMNS() function as a way of "counting" the current column.

A lot depends on exactly what you want to do.

[Glenn Kennedy]

=IFERROR(VLOOKUP($A10,'P&L MedSci'!$E$5:$T$85,columns($A:I),FALSE),0)
will return 9. As you copy across it will return 10, 11, etc.