Vlookup Column Index trick

How to use a vlookup column index trick to allow for easier changes to what the vlookup functions brings back.

NEW: Go to  VLOOKUP Explanations with downloadable exercises and detailed solutions


Vlookup Column Index trick

One of the problems with the VLOOKUP function is how information is pulled through by making reference to the column number in table of information. The problem with this is if changes are made to the spreadsheets these column numbers do no adapt for that, and as a result it can be very time consuming to correct all the functions. Or even worse errors can be generated through ignoring the effects of the changes. In this segment you’ll learn a Vlookup column index trick to make it easier and safer to make changes to a spreadsheet that has VLOOKUP functions in it.

In this example we have some information with regards to students, their Student Numbers and their Marks achieved in recent exams. What we’d like to do is build a little lookup section where we can type the student number in, and all the information will be pulled through.

To do this we’d use the VLOOKUP function,

so if we activate the Function Wizard
the Lookup Value is going to be the Student Number
and because we’re going to copy it across we want to anchor it so it stays looking at column A and we just put in a correct dollar sign
and it needs to look at this table here
and again we make absolute references,
because were looking for the name its column two so we type in 2
and we want it to be false because we want to find an exact lookup
and you click Ok,
We now hit our first problem, because if you want to now take this function and copy it across, because of the way the formula’s has been set up with a hard coded reference to a column number, all these cells will now look at column 2, and what you’d prefer is if the surname looked at the surname column which is column 3 etc etc

So unfortunately you need to go manually through and make the changes.

There is also another problem, what happens if you’ve forgot to put for example the English mark in,

you click here
you insert a column
you put in English
and well just put a mark in for this person
what you’ll see happening down here is the Math’s mark is coming up here
and it says pull through column four, if you go here 1, 2, 3, 4,
the Math’s mark is pulling through the English mark,
So unfortunately what it’ll involve again is

going here
copying all the way across
and then setting up the column numbers again.
A way around this is to do the following,

above the data table write the column number in the relevant cell
so for example in your VLOOKUP this’ll be column one so you put a 1 there
this’ll be 2, 3, 4, 5, 6
Now when you build the VLOOKUP function you do the following

again going through the Function Wizard
the Lookup Value we point to
and we’re goanna make it an absolute reference on the column
the Table Array we highlight the table we are looking at
and we make it an absolute reference
and the Column Index Number instead of saying number 2, we refer to this cell here
and the Range lookup again is false
and we say Ok
Now when you want to copy the cells across what will happen is each VLOOKUP will actually look at its own column and find the number in there. So what is important now is that these numbers accurately represent the column they are in, and that way in one move you can copy across the entire row and get the VLOOKUP to be working

What happens when you insert a row?

Well if you insert a row in the same place,
again it’s the English mark
and we’ll just put an amount here
you’ll see that it is still the same problem because what is happening is it is being told this number here says look at column four which is 1, 2, 3, 4
but now instead of having to change everything here , you can just copy this one across
and you just make sure this one is correct so you just update this,
and there we have the VLOOKUP sorted out,
Now obviously for this small example you could have done it on your own but when you use hundreds and thousands of VLOOKUPS, this will save you a lot of time and just make the whole process a lot safer.

Just as an aside, you may have noticed that the average did not change when we put this number in. The reason for this is that we inserted a column right on the border of a calculation. So for example

· this is an average calculation looking at those two

· because we inserted the column here it was not included

and this is how easily errors happen in Excel so its important to be careful when your inserting columns or rows.