VLOOKUP returns incorrect data

I am using VLOOKUP to determine addresses from a master list.  However I have 
found that if the Lookup Value is NOT in the Table Array, I will get 
incorrect information.

For example if the Table Array has entries for Schmidt and Schultz, and I 
attempt to lookup Schnell (which is not in the Table Array), I will get the 
value for Schultz, which is incorrect.

How can this be avoided?  Or, how can I get a message saying the Lookup 
Value was not found - rather than getting incorrect results?
0
Utf
6/3/2010 10:22:15 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

2 Replies
1132 Views

Similar Articles

[PageSpeed] 3

You would have missed out the last argument of VLOOKUP() Range_lookup which 
is a logical value that specifies whether you want VLOOKUP to find an exact 
match or an approximate match. If TRUE or omitted, an exact or approximate 
match is returned. If an exact match is not found, the next largest value 
that is less than lookup_value is returned..

=VLOOKUP(A1,Sheet2!A:D,2,FALSE)

OR

=VLOOKUP(A1,Sheet2!A:D,2,0)

If no entry is found it returns NA# . To handle this use IF() ISNA() 
combination as below...Replace vlookup_formula string with your actual formula

=IF(ISNA(vlookup_formula),"Not Found",vlookup_formula)


-- 
Jacob (MVP - Excel)


"burtlake" wrote:

> I am using VLOOKUP to determine addresses from a master list.  However I have 
> found that if the Lookup Value is NOT in the Table Array, I will get 
> incorrect information.
> 
> For example if the Table Array has entries for Schmidt and Schultz, and I 
> attempt to lookup Schnell (which is not in the Table Array), I will get the 
> value for Schultz, which is incorrect.
> 
> How can this be avoided?  Or, how can I get a message saying the Lookup 
> Value was not found - rather than getting incorrect results?
0
Utf
6/3/2010 10:22:16 AM
Hi,

Try this

=if(iserror(vlookup(A3,B4:100,2,0)),"",vlookup(A3,B4:100,2,0))

-- 
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"burtlake" <burtlake@discussions.microsoft.com> wrote in message 
news:CA158CBF-5D05-4AD0-8EAE-B9F08D0E8EC0@microsoft.com...
> I am using VLOOKUP to determine addresses from a master list.  However I 
> have
> found that if the Lookup Value is NOT in the Table Array, I will get
> incorrect information.
>
> For example if the Table Array has entries for Schmidt and Schultz, and I
> attempt to lookup Schnell (which is not in the Table Array), I will get 
> the
> value for Schultz, which is incorrect.
>
> How can this be avoided?  Or, how can I get a message saying the Lookup
> Value was not found - rather than getting incorrect results? 

0
Ashish
6/3/2010 10:59:49 AM
Reply:

Similar Artilces:

Project accounting
I have a query raised by one of my customers in relation to the vat 100 report. In quarter 1 this year (Nov 08 – Jan 09) , the Tax return exception report – General Ledger displayed billing transactions that refer to bills generated for external jobs in the project accounting module. These 3 entries also appear in the standard sales tax detail report. This resulted in my customer mistakenly repaying these amounts twice. Anyone any ideas why these transactions should be reported twice Jean -- JB Should have said this is in V10 Jean -- JB "JB" wrote: > I have a query ra...

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

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

Restoring data to Exchange
Hello, We are trying to restore our exchange data after doing a successful Active Directory domain rename. Since Exchange is on a domain controller, we were forced to remove it to complete the domain rename. So, we used ntbackup to backup all of the data. Now, we are trying to restore the data, but we get the following message: <!-- Begin Message --> Unable to restore Exchange data to HHNSRVR\Microsoft Information Store\First Storage Group, check the application event log for more information <!-- End Message --> As it says, we looked in the event log and found this: <!-- B...

How do I get SQL data to show CRLF instead of question marks?
When I query my SQL database from Excel (currently 2007) I have some fields that have carriage-return-line-feed characters and tab characters. These show as square question marks. How can I make the CRLF and TAB characters show up properly (without having to manually edit 5000 rows)? ...

How do I tell Excel to insert data from a cell 4 to the left?
Hi there, Here's the situation. I have a bunch of game prices in cells I8:I15. In cells B8:B15 are the names of these games. I have a cell that gives me the highest price [=MAX(I8:I15)] but instead of showing the price, I want it to show the name of the game. But I don't want to have to enter the name myself, I want Excel to do this automatically so if the prices change it can update itself. Is this possible? [Or does it even make any sense? ;)] Thanks in advance for any help (: Cheers, Jake. Try this... =INDEX(B8:B15,MATCH(MAX(I8:I15),I8:I15,0)) Note that i...

Users mailbox locked because server will not purge data
We have a user who tried to send an email with a 38 MB attachment (after repeatedly instructing her that we have a 3 MB attachment limit) which caused her account to not allow her to send or receive emails since she had gone over her mailbox limits. She moved the email from her Sent Items to her Deleted Items and then emptied her Deleted Items folder. The user is in one of our remote offices. When she checks her folder size, her Local Data only shows the size of her mailbox as 160 KB but when we look on System Manager it still shows her mailbox size as 38 MB. She emptied her deleted items fold...

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

Sorting Data Automatically
I am importing text into a new worksheet and would like to run a macro that can perform labour a labour intensive sort/deletion. Column A contains a mixture of text strings as follows: QR4567 QR4567/QT1223 QR4290 QR4290/QT1224 What I would like to do is determine how many QR's have associated QT's and how many QR's are remaining that dont have associated QT's. Any assistance would be greatly appreciated. Alan Bartley Sydney Australia Hi Alan not sure how the sorting comes into this - sounds more like needing a formula to count when a cell contains both QR & QT as op...

Creating a word2000 document with access 2000 data
I have a database in access 2000 it is basically customer info. address and dates and timesof appointments. I need to place a button on the formpage that prints a letter using the customer details and the date & time entered in the database. Anyone have any clues as to the easiest way to do this.sort of done it using mailmerge but not what I want. Actually want a word document to pop up that the staff can just double check and press print. Any help would be appreciated even just a point in the right direction to a good source of information. to see if the following website's offer...

stack overflow problem with _bstr_t data type
According to MSDN description, passing a lengthy string( of type char*) to a _bstr_t data type may result a stack overflow error. So, why it happens, and what is the maximum length of the string that we can pass thanks in advance, Naren ...

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

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

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

Incorrect SUM
All of a sudden, my Excel can't add two identical columns and get the same answer. Quadruple checked, started over numerous times (won't tell you how long it took to figure out what the problem was.) Very simple spreadsheet. Any thoughts? Dave B Likely some of the "numbers" in one of the column are actually text instead of numbers. Compare COUNT() of the two columns. To convert text to a number, copy an empty cell, select the data range and Edit|Paste Special|Add. Jerry Dave B wrote: > All of a sudden, my Excel can't add two identical columns and get the...

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

Inventory returns purchased on CC
What is the recommended process for the following situation: 1) purchase inventory item using a credit card 2) item is returned to the vendor 3) credit from vendor is put back on the credit card Presumably, need to do a return with credit transaction. This puts the credit on the vendor account. What next? Obviously there needs to be a transaction to remove the credit from the vendor account and another? transaction to reduce the cc balance. Seems like there should be a shorter method for doing this vs 3 different steps! What is the best way to do this? Thank you! Neil ...

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

sort data
i am trying, unsuccessfullty, to sort some data (a small example is below) so I can graph it. I want to put it into a pivot table format but don't want calculations, just the actual data. any ideas??/ Location DATE Result WAT002WA ALBANY BOTTOM 19-Jul-05 0.83 WAT002WA ALBANY BOTTOM 23-Jun-05 0.8 WAT002WA ALBANY BOTTOM 28-May-05 1.16 WAT002WA ALBANY BOTTOM 02-May-05 0.93 WAT002WA ALBANY BOTTOM 06-Apr-05 0.66 WAT002WA ALBANY BOTTOM 11-Mar-05 0.59 WAT002WA ALBANY BOTTOM 13-Feb-05 0.54 WAT002WA ALBANY BOTTOM 18-Jan-05 0.62 WAT002WA ALBANY TOP 09-Mar-07 0.73 ...

Overlapping data series in 2 axis chart
I've got a chart with a primary and secondary axis, trend lines and bars. When it is plotted, the primary bar has been placed on top of the secondary axis bar. I have tried adjusting the gaps and overlap but this doesn't seem to have worked. Can anybody help please? Thanks Diane Jon Peltier has some instructions on his web site: http://www.geocities.com/jonpeltier/Excel/Charts/ColumnsOnTwoAxes.html Diane wrote: > I've got a chart with a primary and secondary axis, trend > lines and bars. When it is plotted, the primary bar has > been placed on top of the ...

refresh data on Status bar
I would like to refresh some data in Status Bar. I use window.SetInterval() to refresh, but it doesn't work for me in this case. My code are as following: var TimerID function DisplayServiceName() { try { var qsRules = qsBridge.RequestQSRules(); sql="select *..."; rs = qsRules.OpenRecordSet(sql, true); txtServiceName.value =rs("ServiceName").value; } catch (err) { txtServiceName.value ="empty"; } } function doStartup() { TimerID=window.setInterval("DisplayServiceName()", 500); } .... <body onload="doStar...

Trouble importing data
Any one know how to merge two columns of data and retain all the info in both independent columns? I've got one column of data data (m/d/y) and another with time (HH:MM:SS) and performing a simple merge won't cut it. Thanks for any suggestions. Mark, If they have bona fide dates and times (not text) you can add them. To to that, a third column could contain = A2 + B2. Copy down. You can now format this column for both date and time if you wish (Format - Cells - Number - Date). Or you permanently add one column to the other. Select one column, Copy, select the other column, Edi...

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

Getting access to dialog data
Context: VC++ 6 , MFC Dialog-based App The basic classes created by AppWizard are three utility classes CChildFrame, CMainFrame, , CAboutDialog and three (I'll call them) program classes, CMyApp, CMyDoc, CMyView. What's visible when the program starts is a dialog that's an instantiation of the CMyView class. Using File | New or the New-File button on the toolbar invokes CWinApp::OnFileNew() which instantiates another member of the CMyView class with data initialized in CMyView::OnInitialUpdate(). This seems to be a perfect way to allow the user to clone an existing dialog ...

Data for Chart with Blank Formula Cells
I am trying to chart a worksheet that contains many formulas. When th result of the formula is zero, the cell is instructed to leave the cel blank (which is important to the information being tracked). However when I chart the data, it charts a ZERO for the cell. I tried the interpolate feature, but because the cell has a formula i it, it is not being read as "blank", even though the value is blank. Help! If week one is 2, week three is "blank" (remember, cell actuall has a formula that is returning the blank), and week four is 6, I do get my chart to interpolate from...