[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

excel

Posted on 2012-08-15
10
Medium Priority
?
364 Views
Last Modified: 2012-08-15
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
0
Comment
Question by:doctorbill
  • 4
  • 3
  • 3
10 Comments
 
LVL 8

Expert Comment

by:Elton Pascua
ID: 38295277
You need to make your lookup range absolute:

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

Open in new window

0
 

Author Comment

by:doctorbill
ID: 38295467
Sorry - still not doing it
Can you tell me EXACTLY how to autofill using the above modification
0
 
LVL 3

Accepted Solution

by:
motaz_mohamed earned 668 total points
ID: 38295489
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 8

Expert Comment

by:Elton Pascua
ID: 38295491
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
 
LVL 8

Assisted Solution

by:Elton Pascua
Elton Pascua earned 1332 total points
ID: 38295504
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
 

Author Comment

by:doctorbill
ID: 38295517
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
 
LVL 3

Expert Comment

by:motaz_mohamed
ID: 38295525
All you need to do is remove the $ sign from the row reference: =VLOOKUP(A2,$H2:$I3202,2,FALSE)
0
 
LVL 8

Assisted Solution

by:Elton Pascua
Elton Pascua earned 1332 total points
ID: 38295546
Please check the file and let us know if this is what you intended.
Compare-Grasp-Traf-T5.xls
0
 

Author Closing Comment

by:doctorbill
ID: 38295565
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
 
LVL 3

Expert Comment

by:motaz_mohamed
ID: 38295574
You're welcome, glad we managed to help!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question