We help IT Professionals succeed at work.

excel

doctorbill
doctorbill asked
on
I am having a very strange issue with my vlookup in the attached spreadsheet
The vlookup in column J is working fine but when I drag the cell formula down to the cells below in order to autofill the cells (using the drag handle on the lower right) the cells do not fill in correctly. If I drag for a few cells and then double click the cells to bring up the cell formula and then hit the enter button the cell fills in correctly
Any ideas what is happening with the autofill ?
Compare-Grasp-Traf-T5.xls
Comment
Watch Question

You need to make your lookup range absolute:

=VLOOKUP(A2,$H$2:$I$3202,2,FALSE)

Open in new window

Author

Commented:
Sorry - still not doing it
Can you tell me EXACTLY how to autofill using the above modification
I checked the Excel sheet and believe the problem is that the calculation method for your file is set to manual. If you change that to Automatic the problem should be gone.
Put this formula in J2 and then drag down:
=VLOOKUP(A2,$H$2:$I$3202,2,FALSE)

Open in new window


To get the range to be absolute, press F4 while the cursor is on the cell address until you get the two dollar signs (one before the column letter, one before the row number).
Motaz is also correct, that's possible. You can press F9 to recalculate or to permanently set to automatic, go to Tools > Options > Calculation - change to Automatic.

However, take note that your lookup formula might need to be changed.

Author

Commented:
If I insert the formula in the J2 cell and drag down, then click on SHIFT - F4, I get the following:
Microsoft excel cannot find the data you are searching for
All you need to do is remove the $ sign from the row reference: =VLOOKUP(A2,$H2:$I3202,2,FALSE)
Please check the file and let us know if this is what you intended.
Compare-Grasp-Traf-T5.xls

Author

Commented:
Thanks so much for your help - this was very frustrating for me as all previous vlookups were working ok and I could not see where the issue was
You're welcome, glad we managed to help!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.