Excel 2010 - Insert column before another with VLOOKUP

Posted on 2014-08-12
Last Modified: 2014-08-12
Dear Experts,

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


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


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)

Question by:csehz
    LVL 47

    Accepted Solution


    pls try  INDIRECT


    LVL 24

    Assisted Solution

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


    Becoming the following using Rgonzos mod:

    The IFERROR removes the repeat of the formula
    LVL 1

    Author Closing Comment

    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..

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
    Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now