up vote 1 down vote favorite
share [g+] share [fb]

I need to perform a VLOOKUP, but the Lookup Value is a cell that is populated based on an other formula. The lookup is bringing me back a value of "#N/A". When I recreate the same values in another Excel file (populating the cells manually instead of by another formula) it brings back the value I need. Any suggestions?

link|improve this question
0% accept rate
1  
First of all, you need to check if the formula in the reference cell is actually giving you the correct value and not giving you #N/A. I did a quick test of the scenario you presented and vlookup was still able to work for me. I am using Excel 2010. Which are you using? On top of that it would help if you gave us an example of the formula that your reference cells have. – Jay Dec 27 '11 at 18:04
I am using Excel 2007 on a Windows XP Professional operating system. My Vlookup formula is as follows: =VLOOKUP(C16,Lookup!A:B,2,) My cell of "C16" is populated based upon the following formula =IF(K16="","","1/1/12") – sarah vac Dec 27 '11 at 18:20
First of all, your vlookup formula doesn't look right. Your second argument which is "Lookup!A:B" shouldn't be a valid argument which is probably why you are getting the error. – Jay Dec 27 '11 at 20:15
2  
The VLOOKUP is configured to do an approximate match. Is that what you intend? (add a final parameter of 0 or FALSE to make it an exact match). By the way, the strategy of using a formula to determine the lookup value is fine, the problem will be in the details of the implementation. – chris neilsen Dec 27 '11 at 21:53
feedback

2 Answers

up vote 1 down vote

Disclaimer: As Jay has stated, it is difficult to understand exactly what you are trying to do. I was able to duplicate the #NA error you were seeing and began to research it from there. There are too many variables within your workbook for me to know and verify if this will work for you. Hope it will, but if not, sorry.

What I found was the error being due to the date format. The IF statement is placing the value as text with quotation marks when the statement is TRUE. That is the correct way to use it. So, here is what to do to make the VLOOKUP formula work. Use the DATEVALUE function to get it to see the date in the correct format.

=VLOOKUP(DATEVALUE(C16),Lookup!A:B,2,)

Please provide feedback so we know if this works for you.

link|improve this answer
feedback
up vote 0 down vote

The question you are asking is still a little confusing even when you give us the formula you are using. It would be additionally helpful if you were to give perhaps a overview of what you are trying to do.

I will try to answer by on what you have told us but if you provide additional information, I will alter my answer to reflect your changes. Base on the information you have given us, this is what I can conclude:

  • Base on the information you already gave us, I think I can assume that the information in Column A are dates in the format "xx/xx/xx". And you are trying to find a date that matches either "" or "1/1/12" from that column and print out the values in column B. I don't completely understand what Column K is. But if this is correct, one thing i would check is the formatting of column A. The values of C16 are text values, "" and "1/1/12". If the values in column A are in date format, then you will get #N/A because there are no match.

If you provide us with more information I can provide more insight into your problem.

link|improve this answer
2  
The Lookup Array can be A:B. This uses entire columns A & B for the array. I have used it. Other than that, I agree with you about the formating of the date. Also I think the VLOOKUP is having trouble with the quotations around the date in the IF statement in C16. Notice the VLOOKUP works when the date is typed directly. – CharlieRB Dec 27 '11 at 20:42
I know that you can look up the whole column of A and B with A:B. I was just noting that "Lookup!A:B" itself is not a valid argument(exactly as the was typed). Unless "Lookup!" is a keyword I never came across... – Jay Dec 27 '11 at 21:00
OK, I see what you are saying. For me, I read it as being the tab name in the workbook. – CharlieRB Dec 27 '11 at 21:15
Hahaha I feel stupid now. Yea it is the tab name from a different sheet in the workbook. I got confused because there is actually a function called lookup and I thought he was incorrectly using that function imbedded within the vlookup function. – Jay Dec 27 '11 at 22:11
@Jay FWIW you can always edit your answers to improve or correct them. You can also delete comments. – Rachel Hettinger Dec 28 '11 at 6:47
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.