Vlookup not updating automatically
One is a straightforward, simple approach, the second is using the columns function, and the third is using the MATCH function.
Here we have an employee LOOKUP table with the employee ID on the left.
And what I should be able to do now is copy that across into each of these cells (D7: G7).
What I’ll do is go Control C for copy, and Control Alt V for paste special because I don’t want to overwrite the formatting, so I’m going to select paste formulas. (See screenshot below) So now we have the LOOKUP table completed using the simple approach.
By the way, if you want to learn more about data validation, you should check out my book “Power Tips for Excel.” That takes you through some data validation tips and tricks.
And what the function COLUMNS does is it counts the number of columns in a particular array, where an array is just a group of cells on the worksheet. I’m going to lock the range reference to the column B, by inserting a dollar sign ($B9: C9).
That means when it is copied across, it will always reference cell B5.
For the table_array, I will select the data excluding the column headings (as before, this is good practice when writing VLOOKUP formulas).
Watch our 14 minute video to help you understand three different ways of writing the VLOOKUP formula for exact matches.
Click on the video below to play it and listen to my audio commentary.
If you find that you can’t use this workbook on your version of Excel, please let us know using the comment section below.