Vlookup not updating automatically

Rated 3.97/5 based on 563 customer reviews

Press Control Shift Right arrow, Control Shift Down arrow, and that will select all the data in the table (this works because there are no empty data cells).

Press the F4 key to lock that to absolute references.

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.

And now, for the column index number, for “Full Name”, I want it to come out with a 2.

For the table_array, I will select the data excluding the column headings (as before, this is good practice when writing VLOOKUP formulas).

As usual I have included a sample workbook with a table of fictional employee data for you to download and follow along with the examples.

Image Credits: In order to follow along with the video and article please download the Sample Workbook “VLOOKUP_Employee_Table.xlsx” by clicking here Note: the sample workbook has been tested on Excel 2010 (32-bit, English language version) and you may get different results in other version of Excel.

Now, you see it’s just put the full name in each of these boxes, which we don’t want. But it does mean that you have to manually enter 2, 3, 4, 5, and 6 as the column offset because when you copy and paste the VLOOKUP formula the col_index_num does not automatically update for you.

Instead, I need to go up here, and change the column index number from a 2 to a 3. Method two uses the columns function, which you can see here.

Leave a Reply