Problems with VLOOKUP Function in Excel
Problems with VLOOKUP Function in Excel
(OP)
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?





RE: Problems with VLOOKUP Function in Excel
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Problems with VLOOKUP Function in Excel
Thanks.
RE: Problems with VLOOKUP Function in Excel
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.
RE: Problems with VLOOKUP Function in Excel
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.
RE: Problems with VLOOKUP Function in Excel
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).
RE: Problems with VLOOKUP Function in Excel
Dik
RE: Problems with VLOOKUP Function in Excel
=VLOOKUP($B8,'Option Chain'!$A$4:$C$196,2,FALSE)
RE: Problems with VLOOKUP Function in Excel
That appears to have done the trick. Thank you very much!
RE: Problems with VLOOKUP Function in Excel
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/
RE: Problems with VLOOKUP Function in Excel