vLookup, Look up Value

Hi,

I have problems when the look up value in the vLookup formula is a date, and 
the cell has a differente format. 

For example, I specify 03/03/10 as the look up value, but the cell has short 
date format, so the real value in the cell is 03/03/2010. As a result, the 
formula displays #N/A.  How can I solve this?

Thanks in advance

Regards,
Emece.-
0
Utf
3/28/2010 10:56:01 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
1238 Views

Similar Articles

[PageSpeed] 33

It would help to see your VLOOKUP() formula.  But I suspect it may look 
something like:
=VLOOKUP("03/02/10",Sheet2!A1:B99,2,FALSE)
and in Sheet2, column A you actually have dates.  So the text you've entered 
( "03/02/10") is treated as text, and not a date, and you don't get a match.

Try a formula like this, using the DATE() function to create the lookup 
value to find:
=VLOOKUP(DATE(2010,3,2),E1:F6,2,FALSE)

DATE( ) need arguments in the order of Year, Month, Day, so 2010,3,2 is same 
as March 2, 2010.

Another option is to actually put the date to match in a cell and then 
reference that cell in your formula, something like
=VLOOKUP(A1,E1:F6,2,FALSE)
with A1 holding the date to match, and E1:F6 being your lookup table.


"Emece" wrote:

> Hi,
> 
> I have problems when the look up value in the vLookup formula is a date, and 
> the cell has a differente format. 
> 
> For example, I specify 03/03/10 as the look up value, but the cell has short 
> date format, so the real value in the cell is 03/03/2010. As a result, the 
> formula displays #N/A.  How can I solve this?
> 
> Thanks in advance
> 
> Regards,
> Emece.-
0
Utf
3/29/2010 12:50:01 AM
Reply:

Similar Artilces:

Looking into software
Hi. My company is a computer retail store / service business (onsite / instore repairs). I noticed that Microsoft Retail Management offers work order support. I cannot find very much information about this feature, and am wondering if anyone reading this is in a similar business, and how the software works for you. -Chris Not very well Try ESC from costal computer corp. www.ccc-soft.com or my pc guys like Atrix "Chris Bullock" <Chris Bullock@discussions.microsoft.com> wrote in message news:FE5F0082-2A95-447A-99D3-8DE89C4847D3@microsoft.com... > Hi. My company is a co...

Looking up data within a collumn
Hi, I am trying to look up data within a column by using a unique data within that same column. For Example, the data will be listed as shown: Column A Name1 Phone1 Address1 Name2 Phone2 Address2 Now I want to be able to just type a name in Collumn D, and have the phone number print out in Column C, and the address in Collumn E. Is there a way to do that? Column A won't be large, under 200, so load time is not a big concern. -- Demitre ------------------------------------------------------------------------ Demitre's Profile: http://www.excelforum.com/member.php?action=getinfo&...

using a date in vlookup
i want to perform a vlookup using the Now() function to generate the lookup value (A1), the 1st column in the table [col B] array will be all the dates in a year listed consequtive,, and the 2nd column being a value assigned to each day in the 1st column [B]. My formula is vlookup(A1,B1:C367,2). The result I get is "#N/A. What am i doing wrong? Thanks Tonso NOW() returns both the date and the time, so you would be better off using TODAY(), which only returns the date. Another problem might be that your dates in column B are really text values that just look like dates - see what happe...

Newton Funds showing incorrect values
Previously it has been identified that there's a problem when downloading values for Newton Funds. The suggestion was that this wasn't unusual and would be corrected. However, it's still a problem and I wondered whether anyone knew when the correction was likely to happen! I'm using MONEY 2004 on Windows XP No, we don't know. It might be worth raising an UK MSN Money problem ticket so they know you're waiting on it, if you haven't already. (http://money.mvps.org/faq/article/322.aspx) -- Glyn Simpson, Microsoft MVP - Money http://money.mvps.org/ Check http:...

Mouse Move response based on combo box value
Hello, all. I have been asked to add a feature to an exiting form where a "note" will appear on mouse over only if a combo box of a continuous form is a particular value (i.e. Annual Safety Training). The main form is frmEmployees, the subform is subformClassesAttended. The combo box is Combo12, but it is displaying the text description (colmun 2) associated with a bound field of a number type, classID. I have a hidden label, lblInfo, with the info to be displayed but am not sure of the coding & whether I should be referencing the description in Combo12 or Class...

I am looking for job cv where do I find them?
Nigel If you open MS Word, at the right side of the screen are links to templates online.. in Publisher, click on TOOLS - TOOLS ON THE WEB.. when the window opens, from the section 'related links', select 'Microsoft Tools on the web'.. it will take you here.. http://office.microsoft.com/en-ca/templates/default.aspx?DPC=%7B90280409%2D6000%2D11D3%2D8CFE%2D0050048383C9%7D&DCC=%7B8E46FEFA%2DD973%2D6294%2DB305%2DE968CEDFFCB9%7D&AppName=Microsoft%2520Word&HelpLCID=1033&CLCID=1033 And please do not type entire questions in the subject line.. had I pasted all...

vlookup excel and access...
assuming i have this code, is possible to use this vlookup withnthe adta into mdb access?... old scenario: Private Sub TextBox25_Change() Dim CODICE As Integer Select Case Me.TextBox25 Case "" Me.TextBox4 = "" Case 1 To 8 CODICE = Val(Me.TextBox25) Me.TextBox4 = Application.WorksheetFunction.VLookup _ (CODICE, Worksheets("TABELLA").Range("Q2:R9"), 2, False) Case Else Call MULTI_LINE_BOX End Select End Sub new scenario: Inested column Q and R in excel i have created a mdb into: \\my server\myserverdir\USER.MDB and into this mdb have inserte a table U...

Vlookup within a vlookup
I am trying to lookup a cell within a table - but the table to use is found in another table. =VLOOKUP(B3,VLOOKUP(B2,F3:G9,2,2),2) Cell B2 is a dropdown box allowing one of the choices in colum f below. Column G represents which table to use for the initial lookup based on your choice in the drop down dox. column f column g Alt A 30 Yr fixed30 Alt A 15 Yr fixed15 All I get is an error - can someone help ? Thanks, Yosef It sounds like you would need to use INDIRECT within the lookup formula http://tinyurl.com/czxtt that thread shows the way to do it exce...

Options reset to values of most recently opened workbook
I've noticed a behavior in Excel 2000 SP-2 on Windows 2000 Professional SP-3 and I'm not sure if it is an error, by design, or correctable by some option or setting I'm missing When I open multiple workbooks I've found that the options settings under Tools/Options for the most recently opened workbook apply to ALL open workbooks. It might be best to explain by a couple of examples 1. I normally have the "Windows in Taskbar" option checked so I can view each of my open workbooks as a separate item in the taskbar. I have all of my workbooks set up this way. If I r...

Trend Line constants as Excel Cell Values
I want to put the constants of a 2 degree polynomial trend line into Excel Cells. Are there formula's to do this? Equation is Y = Ax(2) + Bx + C. I want the value of A,B and C in seperate cells. Can anyone suggest how this can be done? Thanks -- Ken Hi Ken, Supposing your x-range is in A2:A10, and y-range in B2:B10, select a 3-column x 1-row area and enter the following formula and confirm with CTRL-SHIFT-ENTER. =LINEST(B2:B10,A2:A10^{0,1,2},0,) Regards, B. R. Ramachandran "Ken" wrote: > I want to put the constants of a 2 degree polynomial trend line into Excel &g...

Lost on Vlookup, match, etc....
Can someone walk me through this please? I a workbook that imports a years worth of data from filemaker to be analyzed and charted in excel. It contains several sheets, but I am concerned with worksheet 1 (daily data) and worksheet 2 (bodyweight). Daily data contains the raw data I pull in from Filemaker. It results in a table with a row for each day of the year. It has 12 columns, but in this instance, I am only interested in 2 of the colums Column F, (Date), and Column R (Bodyweight). There is only one entry per week for body weight. The bodyweight sheet has 3 columns (week #, date, and w...

How do I sort worksheet without affecting formula values?
I have two worksheets, one of which has formulas which reference the other. The problem is, I cannot sort the first worksheet and have the formula values in the second worksheet follow it so the values stay the same. I have tried making them absolute references ($A$1) but it doesn't help. I feel your pain. I've ran into the same issue before. The problem is the use of worksheet functions. The solution is DO NOT USE worksheet functions. Use a VBA subroutine instead. Here is an example: With worksheet functions.... A B ...

Looking for Outlook add on program
HI all I recently had a hard drive fail and lost a program but i am sure somebody will point me in the right direction. It was an add on for outlook that was as far as I am aware freeware and was called something like AOPEN It sat it's self in the options panel of Outlook and allowed you to chose what files/attachments could or could not be downloaded. Do any of you have an idea what it was called or as to where i can download it again ???? Many thx Steve This was to open attachments blocked by security? Was it called Attachment Options maybe? See http://www.slipstick.com/o...

VLOOKUP in VBA
On the worksheet I can insert in a cell =VLOOKUP(C5, Hobokee.xls!AcsLow, 2) and it works perfectly, looks up the value in column 2 of the range named AcsLow in the same workbook. But elswhere I want in a macro to lookup the same table and assign the result to a variable BalAmt. BalAmt = VLOOKUP(AccNum, Hobokee.xls!AcsLow, 2) does not work. It gives a function not defined error on Hobokee. If instead of Hobokee I put Workbooks("Hobokee"), it gives function not defined error for VLOOKUP which it changes to VLookup. I have been overVLOOKUP in the Help file and see nothing wrong. W...

Scatter Chart with non-numeric values on X axis
Hi, I'm trying to figure out how to create a scatter chart (graph) wit words for the x axis. I'm trying to setup a graph showing availabilit of servers, so for the Y axis I want to show a percentage (betwee 1-100%), and for the X axis, I want to show the name of the servic (mail, news, etc). However, it seems that with a scatter chart I ca only do numeric values on both axis. Is there any way around this? Thx -- penas ----------------------------------------------------------------------- penasm's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1583 View...

VLOOKUP problem
I down a list of top 100 stock symbols to Excel and place them in Column A and their rank in Column B (1-100). the next week I download a new list of the top 100 stock symbols and their rank to Column C and D respectively. then I create column E using the function =VLOOKUP(C4,A:B,2,FALSE) Which tels me the rank each stock had last week and if a stock is new this week it puts "#N/A" in the appropriate cell in column E. How do I get the Vlookup function to put "NEW" in column E if the is new to the list and wasn't in the list last week? Ed =if(isn...

need to find which numbers (3+) in a column sum to a value
need to find which numbers (3+) in column sum to a value I have a column of 100+ numbers. I know that the column should sum to x, but is summing to y instead. I need to find which numbers in the column sum to the difference of x and y. Then I can remove them from my column. Thanks. This is difficult, because you need to check all the combinations of those 100 numbers to find your difference. The number of combinations from a pool of 100 numbers is, frankly, staggering: 2 to the 100th power, or 1,267,650,600,228,230,000,000,000,000,000. Can you reduce this list? If you know the diff...

Take a look at this important pack from Microsoft Corporation
--uqqkvddiqp Content-Type: multipart/related; boundary="chkhrtdwvcmek"; type="multipart/alternative" --chkhrtdwvcmek Content-Type: multipart/alternative; boundary="ilyjfqiirp" --ilyjfqiirp Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Customer this is the latest version of security update, the "October 2003, Cumulative Patch" update which eliminates all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express. Install now to protect your computer from these vulnerabilities, the...

Making a worksheet and a graph that doesn't show zero values
Hi I am trying to build a template worksheet for my research. I get a file of logged data per day. I want to be able to copy the logged data from a .CSV file to a template file. Then separate worksheets will look at the raw data and import data from certain columns into that worksheet. The data is not logged in a regular way, there can be 30 cells between logging intervals or 29 or 31 cells. I can select that every cell in a column of Worksheet 1 looks at a set cell in the "Raw Data" worksheet. This will then give me a column consisting of logged data values with zeroes ...

VLOOKUP #40
Would like to post data from one worksheet to another. Could you provide a few more details about what exactly you want to do. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "dr" <drivera@opvista.com> wrote in message news:1f1001c52c08$ad4c33a0$a601280a@phx.gbl... > Would like to post data from one worksheet to another. ...

combobox and vlookups?
I am trying to add a combobox that when you select from the dropdown menu, the columns nextdoor automatically pull up corresponding data that is related to the selection from the dropdown list. Does this make sense? What do I do to set this whole thing up? (I don't know code). Thanks. -- Message posted via http://www.officekb.com You shouldn't need code for anything you described here. Set up the combobox with the list fill range, if any. Set the linked cell to the desired cell. Then, use the VLOOKUP command in another cell to look for the linked cell from the combo box to lo...

Read value in txt file
Hello to everybody I would like to read a value in a txt file. For example, the value will following the egual sign. So I want to look for language and get "fr" as value. language = fr country = france Thank you by advance and have a nice day. <bcastaing@gmail.com> said this in news item news:389dabc3-ca3b-440b-b55a-a93ca9221025@w16g2000yqw.googlegroups.com... > Hello to everybody > > I would like to read a value in a txt file. For example, the value > will following the egual sign. So I want to look for language and get > "fr"...

looking for dbx file format specification
hi, I am looking for dbx file format specification I am aware of the specification of Arne Schloh at http://oedbx.aroh.de, but it seems incomplete and unofficial is there something more complete and more official, describing the microsoft outlook 5 or 6 file format thanks, gjuro namely, the real question is how to write a program to read dbx files, preferably from .net (C# or VB.NET), preferably without any special library but with barebone byte reading, directly from file "Gjuro Kladaric" <gjuro@kladaric.net> wrote in message news:uK3Ci8#xK...

VLOOKUP
This is a multi-part message in MIME format. ------=_NextPart_000_0001_01CAC8EE.B1306170 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hello, I use the VLOOKUP function to pull basic data from external data sheets, currently an example of my command looks like this: =VLOOKUP($A5,'[RT NP 67 MF.xls]ODD'!$A$1:$S$250,$L$1,FALSE) The "$AA%" is the data I am looking up and the "$L$1" is a variable to the column I am wishing to insert. The question I am trying to get an answer for concerns...

Collecting Range Name values to VBA
I have a worksheet "Setup" where users type in a date in a cell named "ChtDte" and a path and database name in a cell named "FLName". I am using DOA to connect to a database and return a record set. The query used "qryCOCostwRates" uses a date paramater. Because this sheet will be used by several users all pointing to the database in different locations, I need to know where they have the database. I need to get the values in these two range names in the setup tab of the spreadsheet so I can connect to the data, and provide a value for ...