why this strange result with vlookup

hi i have this formula in a cell =VLOOKUP(A3,'LookUp Data'!A2:O740,2,FALSE)
but any time i pick a code from my list items in column A it gives #NA but 
as soon as i change to a different code it gives the name.
 any help why

0
Utf
4/22/2010 6:21:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

3 Replies
1195 Views

Similar Articles

[PageSpeed] 2

My best guess would be a data type mismatch. Looking up text in a column of 
numbers. When you change the value the type gets converted and the result is 
returned.

Hard to say though based on yoru description. The ohter thig to look for 
might be blank characters padded at the end of the input.
-- 
HTH...

Jim Thomlinson


"Amin" wrote:

> hi i have this formula in a cell =VLOOKUP(A3,'LookUp Data'!A2:O740,2,FALSE)
> but any time i pick a code from my list items in column A it gives #NA but 
> as soon as i change to a different code it gives the name.
>  any help why
> 
0
Utf
4/22/2010 6:58:01 PM
Any chance that your values are text and you're trying to match a number -- or
your values are numbers and you're trying to match text.

'123 
is different from
123

And changing the format of the cell isn't sufficient to fix the problem.

Or maybe you have extra spaces in some of the entries????

Debra Dalgleish has lots of notes on troubleshooting =vlookup():
http://contextures.com/xlFunctions02.html#Trouble

Amin wrote:
> 
> hi i have this formula in a cell =VLOOKUP(A3,'LookUp Data'!A2:O740,2,FALSE)
> but any time i pick a code from my list items in column A it gives #NA but
> as soon as i change to a different code it gives the name.
>  any help why

-- 

Dave Peterson
0
Dave
4/22/2010 7:35:28 PM
Try swing it to a text match:
=VLOOKUP(A3&"",'LookUp Data'!A2:O740,2,FALSE)
The &"" bit will convert the lookup value in A3 (which might be a real 
number) into text for consistent matching, w/o impacting text lookup value. 
-- 
Max
Singapore
--- 
"Amin" wrote:
> hi i have this formula in a cell =VLOOKUP(A3,'LookUp Data'!A2:O740,2,FALSE)
> but any time i pick a code from my list items in column A it gives #NA but 
> as soon as i change to a different code it gives the name.
>  any help why
> 
0
Utf
4/22/2010 11:16:07 PM
Reply:

Similar Artilces:

Strange Output
Hello, everybody !!! Why this fragment of code shows : ac cout<<"a\??/??/bc"; Thanks All !!! "Alex Dmitriev" <firejump@mail.ru> wrote in message news:ecTgPp8pEHA.3800@TK2MSFTNGP14.phx.gbl... > Hello, everybody !!! > Why this fragment of code shows : ac > > cout<<"a\??/??/bc"; http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_vccelng4/html/elconTrigraphs.asp '??/' is a '\' so you end up with "a\\\bc" http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_vccelng4/html/elc...

Excel Object showing strange behavior
Hello, I have a sheet that I created that contains an Excel object (small spreadsheet) that when double clicked, will open up and allow it to be populated. After they populate it, and close it,...the data is automatically reflected in the object on the original sheet. This is shared with everyone and it's working great. BUT.....(always the but) Why have a few people sent the form back to me saying that when they double click the object it opens up a "Format Object" dialogue box rather than the editable sheet that it was supposed to do? They sent it back to me and sure enough...

Confine results of web query to one cell
How do you confine the results of a web query to one cell in Excel 2004? Under Excel 2004 a web query always seems to use up two cells in a given column no matter what data range properties I set. I never had a problem with this in Excel 2000. ...

Cell shows formula not the result
I type "=A1+B1" in cell C1. Cell C1 displays "=A1+B1", not the result of the formula. Tried CTRL +` but that just seems to make the colums thinner. It's driving me nuts! Can anyone help? Jeff Hi Jeff... There is either TWO things happening... 1. Remove the quotations...unless you posted them here for display purposes. OR 2. Go to TOOLS/OPTIONS on the VIEW tab (at the bottom) Window Options...Uncheck the box that reads FORMULAS. Let me know how you make out. Jenni Jeff Granger wrote: > I type "=A1+B1" in cell C1. > > Cell C1 displa...

Exchange Permissions--Strange One
Hi by default, domain admins (and ent. admins) are denied "receive as" and "send as" permissions to the mailbox store. additionally, by default, on the exchange advanced tab under the mailbox rights button, domain admins are DENIED Full Mailbox Access. However, if you look at the security tab for each user object, by default domain admins are allowed "send as" and "receive as" permissions. now i know that the "receive as" right is ignored on the ad user object, but i ran a small test. i logged on as a domain admin, opened up the domain admin...

VLOOKUP #2
I'm having some trouble with VLOOKUP in Excel 2000 and after extensive Googling i'm none the wiser. We have an Access 2000 database, some data from which I am exporting into an XLS. I'm trying to x-ref this with a monthly Excel (not initially 2000 but later saved in that format) report, with the tried and tested method of adding a column of VLOOKUP(refno, namedtable, 2, FALSE) to link in a column of information from the database to relevant lines in the report. I'm certain i'm doing it correctly, but in some circumstances it seems two cells seem not to equate when they ac...

Referencing another field if result is N/A
Hello, I have a situation where I am referencing data on another workbook. The reason behind this is the data in Column H can be entered incorrectly and often is. I am able to prevent this going forward but can not do so for the items already entered. I enter a formula via VBA, however, sometimes the result will be #N/A and if so I would prefer to leave the old value and just highlight it that it needs to be verified by hand. I am not sure if I can just "leave" the value as it has pretty much already been replaced. Due to this, I copy the column to a different colum...

Strange Email failure error
Can anyone help? Using Outlook 2002 and POP3 mail. I've always been able to send emails to my wife's work email address until this week, when I get this very strange failure notice. "Connected to 213.2.49.230 but greeting failed. Remote host said: 421 reppsrv01.repp.co.uk is not accepting new connections. Please try later I'm not going to try again; this message has been in the queue too long." Does anyone know what the hell this means? thanks -- Google is your Friend Email address deliberately false to avoid spam: gordonATgbpcomputingDOTcoDOTuk www.gbpcomputin...

Very Strange
Hi I have a Word 2007 document. In this I am using 3 different heading styles. I want to use the feature 'Select Text with Similar Formatting' but it is not working. I have 5 headings that are all Title Case, size 10, Times New Roman, Left aligned and Bold there is exactly the same formatting applied to all 5 headings - I even checked the paragraph spacing - all headings are 0pt before and 12pt after. I checked show and hide - everything I repeat everything is the same - can you tell me why oh why will this feature only select 3 of my headings and not the 5???? ...

produce random results in a report
I'm trying to write a report that will produce random results each time it is run. There are approximately 300 records in my database. For each record, I have included a field with a number identifier (1-7). Each time I run the report, I want it to choose records randomly based on the following list (the number represents the identifier). 2 2 1 7 7 7 5 3 4 6 On 15.12.2009 04:12, DamselNTX wrote: > I'm trying to write a report that will produce random results each time it is > run. There are approximately 300 records in my database. For each record, I...

why this strange result with vlookup
hi i have this formula in a cell =VLOOKUP(A3,'LookUp Data'!A2:O740,2,FALSE) but any time i pick a code from my list items in column A it gives #NA but as soon as i change to a different code it gives the name. any help why My best guess would be a data type mismatch. Looking up text in a column of numbers. When you change the value the type gets converted and the result is returned. Hard to say though based on yoru description. The ohter thig to look for might be blank characters padded at the end of the input. -- HTH... Jim Thomlinson "Amin" wrot...

DestroyWindow ASSERT problem....strange, strange!
Hi I have derived a dialog-class in a DLL (shared MFC) I can construct an object of this class in my app, and when overloading DoModal() and adding an AFX_MANAGE_STATE(AfxGetStaticModuleState( )); call, I can see the CMyDialog dialog. But here is the issue, I have a timer set for a certain time, it posts a message and the dialog has to be killed - so I use CDialog::OnCancel. Then DoModal calls DestroyWindow() - Here a copy of the m_hWnd is made in hWndOrig. #ifdef _DEBUG hWndOrig = m_hWnd; #endif Then m_hWnd gets set to Null by ::DestroyWndow (which calls OnNcDestroy). But later in Dest...

=vlookup on 2 columns
Hello All, Using Excel XP. I have two columns of data, for example, say temps in column A and wind speeds in column B. I want a vlookup formula that would give me a windchill in column C based on a match in column A & B. A B C ------------------------------ 1 47 6 45 2 47 7 45 3 47 8 44 4 47 9 44 5 47 10 43 6 47 11 43 7 47 12 42 8 47 13 42 9 47 14 41 I che...

Strange behavior #3
Hello. I use Outlook 2003 with a internal pop/smtp server (Merak). Using Oulook 2003 (it's the case for all users in the company), when we send mails to internal addresses, in the recipient mailbox our address doesn't appear, we are considered as ghost users! But, if on the same PC we use another email client (Outlook express for example), the mail arrives with my address as sender! Does anyone feel a ghostbuster? Thanks by advance. ...

strange Icon
I'm running Office 2007, and always back up my files to a flash drive which I can then open in my laptop. The "Icons" always look normal until today When I backed up an Excel file. The new Icon on the flash drive has a big exclamation mark (!) in the center of it!! I have reviewed the file and can't find anything wrong with it -- have been doing this for years and have never seen this before. Will someone please tell me what this means!! The icon in my "documents and settings", where the file was saved originally is normal. big john This is a cross post wi...

Addition results to show within same cell?
I don't know what to call what I need to do, so I'll describe the situation. If I type in 100800 in A1, how can cell display that number + 199 so that, although I typed in 100800, the cell displays: 100800 - 100999? I usu. just do separate cells and do an easily addition formula, but I'm finding that it would make life easier if I'm just dealing with one cell for each range of numbers so that I'm just dealing with column A even though there are 2 numbers. Thanks. :oD One way: Put this in your worksheet code module (right-click the worksheet tab and choose View...

strange
hi, why if I load Dialog from satelite DLL it display with white background? When from local resources its ok. thanks ...

Match w/more than one result
I'm using the Index & Match function to show text in column B that is i the same row as my searchword in column A. Formula is : =index(b1:b200,match("John",a1:b200,false),1) This will find a match for "John" in column A and show what is to th right in column B. The problem is that "John" appears more than once in column A and need a formula that shows all matches instead of just the first. Any help is appreciated -- Message posted from http://www.ExcelForum.com for showing all matches, you're probably better off using Data/AutoFilter. In arti...

Strange problems
All, A standalone Exchange 2003 SP2 server acted up the other day and needed to be restated. After the first reboot it only stayed stable for about 10 minutes. After the second, it's been running but generating the errors listed below. I ran the "Performance Troubleshooting Analyzer" and "Troubleshooting Assistant" tools from Microsoft's websites but didn't get anything that was too useful. Any suggestions? Event Type: Warning Event Source: MSExchangeIS Mailbox Store Event Category: General Event ID: 1025 Date: 3/28/2007 Time: 8:44:43 AM User: N/A C...

Vlookup #27
Is there a lookup function that can find the intersection value of a specified column and row based on their headings (column and row)? Hi, Try, =ColumnHeading RowHeading Hope this helps! In article <19c3001c44d48$c7bddf30$a001280a@phx.gbl>, "nc" <anonymous@discussions.microsoft.com> wrote: > Is there a lookup function that can find the intersection > value of a specified column and row based on their > headings (column and row)? I was looking for something more like a lookup function. Plus your method don't seem to work. Thanks for trying. >...

Too Strange!!
Today I had a user receive a NEW email from another user in the company (it showed up as a new email message and displayed the new email notification message) - the strange thing is, the email had a sent date that was over a month old. She tells me that she remembers receiving this exact same email on the actual send date which was over a month ago. I checked her inbox - which is in her personal folders and there is only one copy of it which has the sent date from a month ago. I've seen this happen before but could never figure it out. It's almost as if an old email turns up ...

strange problem with ON_UPDATE_COMMAND_UI handler
Hi all.. I programmed an application that has a dialog containing a tree ctrl. On that tree ctrl I'm showing a popup menu when user right clicks on it. The problem I'm having is concerning enabling and graying of popup menu items. I have this in BEGIN_MESSAGE_MAP: ON_COMMAND(IDM_NUEVO_PRODUCTO_CONSOLA, OnNuevoProductoConsola) ON_UPDATE_COMMAND_UI(IDM_NUEVO_PRODUCTO_CONSOLA, OnUpdateNuevoProductoConsola) The first strange thing is that OnUpdateNuevoProductoConsola handler is being called when I click on the item, and not when the item is shown Another strange thing, is that if I g...

Strange color "smudge"
A cell in one of the spreadsheets I've been working on has developed a complexion problem! I noticed this afternoon a small smudge of color one of the cells near the top. The color is whatever I select in the Windows "Appearance" box for 3D objects. It looks like someone drew it with a sharpie or something. Just a short vertical line, about an 1/8 inch long in the lower left of the cell. If I change column widths of the column it's in or nearby, or change row height, it will be in a different column or row but in the same position relative to the overall window the spreadshee...

VLOOKUP just returns formula
Help, my VLOOKUP is just putting the formula into the cell. =VLOOKUP(A13,'Tasks'!A2:D10,2,FALSE) Where A13 is on same worksheet as the lookup cell. What am I doing wrong? My guess is that you've got the cell formatted as text. Format the cell to General, then go into edit mode & re-enter the formula. -- David Biddulph "Jan Ogg" <JanOgg@discussions.microsoft.com> wrote in message news:FB9EF9D3-33BC-4EB2-90A1-43BCFE02DD20@microsoft.com... > Help, my VLOOKUP is just putting the formula into the cell. > =VLOOKUP(A13,'Tasks'!A2:D10,2,...

This is very strange....
In Windows Live Mail Desktop, my messages are suddenly coming in as text only, even though I've set up to receive HTML from my safe senders list. Also, my MAPI account has mysteriously lost several folders that I set up to store different styles of messages. This has all occurred within the last day or so. Alt + T, Options, Read tab, uncheck Read all messages in plain text. Alt + V, Layout, Folder pane, check Show Storage folders. Look under Recovered items. -- Ron Sommer MS MVP- Windows Live Mail "robertwnielsen" <robertwnielsen@discussions.microsoft.c...