# Excel 2010 - Insert column before another with VLOOKUP

Dear Experts,

Can you please advise I have a formula in a column containing VLOOKUP in column B:

=IF(ISNA(VLOOKUP(\$E:\$E;TotalByPositions!\$Q:\$U;COLUMN(\$C1);0));"";VLOOKUP(\$E:\$E;TotalByPositions!\$Q:\$U;COLUMN(\$C1);0))

but if I insert a column before of that B column, the Excel automatically push the formula columns right into this format:

=IF(ISNA(VLOOKUP(\$F:\$F;TotalByPositions!\$Q:\$U;COLUMN(\$D1);0));"";VLOOKUP(\$F:\$F;TotalByPositions!\$Q:\$U;COLUMN(\$D1);0))

Can you please advise how it should be wrote to not allow this automatical modification? (so \$E:\$E to \$F:\$F and the COLUMN(\$C1) to COLUMN(\$D1) in the formula)

Thanks,
LVL 1
###### Who is Participating?

Commented:
Hi,

pls try  INDIRECT

=IF(ISNA(VLOOKUP(INDIRECT("\$E:\$E");TotalByPositions!\$Q:\$U;COLUMN(INDIRECT("\$C1"));0));"";VLOOKUP(INDIRECT("\$E:\$E");TotalByPositions!\$Q:\$U;COLUMN(INDIRECT("\$C1"));0))

Regards
0

Commented:
Rather than IF(ISNA()) it may be better to use IFERROR()

=IFERROR(VLOOKUP(\$E:\$E;TotalByPositions!\$Q:\$U;COLUMN(\$C1);0);"")

Becoming the following using Rgonzos mod:
=IFERROR(VLOOKUP(INDIRECT("\$E:\$E");TotalByPositions!\$Q:\$U;COLUMN(INDIRECT("\$C1"));0);"")

The IFERROR removes the repeat of the formula
0

IT consultantAuthor Commented:

I was not aware of these techniques so using INDIRECT and neither the IFERROR instead of IF(ISNA..
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.