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
csehzIT consultantAsked:
Who is Participating?
 
Rgonzo1971Commented:
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
 
SteveCommented:
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
 
csehzIT consultantAuthor Commented:
Thanks very much for your fast answer,

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.

All Courses

From novice to tech pro — start learning today.