Transcripts for the above video clip:

VLOOKUP TIPS AND TRICKS

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 technique 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
• go to VLOOKUP
• 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.