excel

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
doctorbillTechAsked:
Who is Participating?
 
motaz_mohamedCommented:
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.
0
 
Elton PascuaCommented:
You need to make your lookup range absolute:

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

Open in new window

0
 
doctorbillTechAuthor Commented:
Sorry - still not doing it
Can you tell me EXACTLY how to autofill using the above modification
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Elton PascuaCommented:
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).
0
 
Elton PascuaCommented:
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.
0
 
doctorbillTechAuthor 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
0
 
motaz_mohamedCommented:
All you need to do is remove the $ sign from the row reference: =VLOOKUP(A2,$H2:$I3202,2,FALSE)
0
 
Elton PascuaCommented:
Please check the file and let us know if this is what you intended.
Compare-Grasp-Traf-T5.xls
0
 
doctorbillTechAuthor 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
0
 
motaz_mohamedCommented:
You're welcome, glad we managed to help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.