Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel 2010 - Insert column before another with VLOOKUP

Posted on 2014-08-12
3
Medium Priority
?
294 Views
Last Modified: 2014-08-12
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,
0
Comment
Question by:csehz
3 Comments
 
LVL 54

Accepted Solution

by:
Rgonzo1971 earned 1200 total points
ID: 40255541
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
 
LVL 24

Assisted Solution

by:Steve
Steve earned 800 total points
ID: 40255666
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
 
LVL 1

Author Closing Comment

by:csehz
ID: 40255698
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Currently, there is an issue with being able to copy values from an external application to a dropdown list in Project Web Access (PWA).  The standard copy and paste methods don't seem to work properly. Here is a way to accomplish this task to s…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question