Problems with VLOOKUP Function in Excel

[nealt]

I have an Excel spreadsheet with multiple cells using the VLOOKUP function. The data queried changes week to week, so the data table has to be cleared out and new data copied into it each week. I imported a data table into the spreadsheet first, then wrote the formulas in a separate worksheet and it worked fine. When I cleared out and recopied new data in, the VLOOKUP function does not work. The cells containing the VLOOKUP function all report "0". The cells containing the formulas are protected and are all still in tact. Can anyone tell me what's going on and how to fix it?

 

[IDS]

I suspect that it is connected with your procedure for importing the data. Can you give us details about how you did that?

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[nealt]

The data is copied from a table on a website and pasted into a data table in the spreadsheet. What's comfusing about it is that it worked the first time, but not the second. Any ideas?

Thanks.

 

[electricpete]

If you do a cut/paste of the original data to another location, the formula's will follow it.

Try looking at the vlookup formula to see if it is referring to the table range that you expect.

Also if you upload your spreadsheet, people may figure out your problem for you.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[nealt]

The VLOOKUP formulas are referring to the correct table range, and the values in the table range are correct. The problem is that the values are not being reported in the cells containing the VLOOKUP formulas.

First of all, a large table of data is copied into the worksheet "Option Chain", cell range G4:S81. Cell range A4:E81 reports selected values from the G4:S81 range. VLOOKUP formulas in worksheets "Bear Call" and "Bull Put" then pulls values from "Option Chain" cell range A4:E81, or it is supposed to. As I mentioned in the original post, I originally copied and pasted the data into "Option Chain" then wrote the formulas and it worked fine. Now, it just reports '0'. I've attached the spreadsheet here.

 

[dik]

Attached spreadsheet... first two columns are identical initially and the first one is used to create a dropdown list.

The last column is an NDX column (index) that is created by copying (1+prev cell) and then using Paste special to convert to values.

The second to fifth column are then sorted in ascending order based on the second column. The NDX column is then 'scrambled' to suit, but the second to fifth columns can be resorted (based on the NDX column) to create the original. This way, I can add new data and re-do the above. (The NDX column has to be incremented to accommodate the new data).

 

[dik]

Forgot to add... I use variable names for the dropdown list value and the lookup table...

Dik

 

[Clyde38]

Does this help?
=VLOOKUP($B8,'Option Chain'!$A$4:$C$196,2,FALSE)

[link

http://www.linkedin.com/in/clydehancock

]

[/url]

 

[nealt]

Clyde38:

That appears to have done the trick. Thank you very much!

 

[IDS]

The new data covers a smaller range than the lookup range (A4:C196), so the end of the lookup column has a series of 0s, and the column is no longer in ascending order. If you don't have ascending (or descending) order you have to use an "exact" match, by entering False (or 0) as the final parameter, as suggested by Clyde38.

For future use you might like to look into using a dynamic named range, so you can deal with longer data sets, as well as shorter ones.

Also I noticed that on the spreadsheet you uploded there was a blank cell at A50 on the Option Chain sheet.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[nealt]

Thanks IDS & Clyde38. I've made the changes in the spreadsheet and it works great!