Solved

# Excel 2010 - Insert column before another with VLOOKUP

Posted on 2014-08-12
272 Views
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
Question by:csehz

LVL 47

Accepted Solution

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

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

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

## Featured Post

### Suggested Solutions

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.