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
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
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
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
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
