98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel Training and help » V Lookup
V Lookup
Resolved · High Priority · Version 2003
Mon 7 Apr 2008, 12:39Delegate Alison said...
Alison has attended:
Excel Advanced course
V Lookup
I have a problem with a V lookup function. The function is =VLOOKUP(A2;[Book1]Sheet1!$A$2:$D$3759;4;FALSE) and the cell to look up is A2
The formula is correct but it just keeps returning #N/A. The reason I know the formula is correct, is that if I click on a particular cell, where I know there is a correct answer, and click the Vlookup Function, and enter, the correct answer is displayed. Then when I ask this to copy down, this new answer is displayed in each cell from then onwards! It is like the copy down is keeping the cell answer and not the formula answer.
The original spread sheet has 8000 lines and the table used in the look up has 3750 lines.
I have tried using a smaller file of the same data with the same response. I use Vlookup alot normally with out a problem.
Can you help
Mon 7 Apr 2008, 17:21Trainer Amanda said...
RE: V Lookup
Hi Alison
Thank you for your question.
Hmm, interesting. The FALSE part of the argument means that there needs to be an exact match to the lookup value that is entered into cell A2, in the leftmost column of your table array (A2:D3759). If there is not an exact match to the lookup value in A2, then you will get an #N/A error.
Does this explain what is happening?
Amanda
Mon 7 Apr 2008, 17:33Delegate Alison said...
RE: V Lookup
Amanda
no it does not I'm afraid. there are two problems, the first that the first cell which is looked up reports back #N/A which is correct, and then lower down there exact matches, and all the cells are coming back as #N/A.
then on another part of the spread sheet, where there are correct matches, it is just returning #N/A
I got around one bit, by saving both files as csv and the first Vlook up worked, but cant get it to again. I am begining to think there is an excel bug!
But if you can shed any light, it would be very helpful
Alison
Mon 7 Apr 2008, 18:29
Edited on Mon 7 Apr 2008, 18:30Trainer Pete said...
RE: V Lookup
Hi Alison, Please excuse me sticking my oar in but have you tried the vlookup without the absolute references in the formula, perhaps using a name for the range, if you have please forgive. regards Pete
Tue 8 Apr 2008, 08:58Delegate Alison said...
RE: V Lookup
Pete
We are a german owned company and normally access excel through our network. My colleague sent the files home last night, saved them to a new work book ran the Vlookup with exactly the same details and hey presto- it worked.
Therefore I believe it is due to the excel we use and the file properties. I am about to ask some questions of our central IT.
thanks for your help
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Formula for last day of monthIn some cases it is necessary to find the last day of a month for a given date. If you use the following formula, you can achieve this, ie; if you have a column of dates, use this formula to find the end of month for each day by using the fill handle. The formula is as follows, and assumes in this example that the first date in question is in cell C5, in any other cell type; =DATE(YEAR(C5),MONTH(C5)+1,1)-1 |
