lookup #3

I am performing a lookup function but in certain 
situations the cell that is looked at is blank.  Is there 
a way to avoid getting the #N/A as the result?

thanks

0
mrfrank73 (32)
1/2/2004 8:51:04 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
492 Views

Similar Articles

[PageSpeed] 41

frank,

=IF(ISNA(yourlookup),0,yourlookup)
Is the basic construct of testing for the #N/A and if your Vlookup would
result
in #N/A then return a zero (can be changed to a blank by substituting two
double quotes
for the 0)

John

"frank" <mrfrank73@aol.com> wrote in message
news:024701c3d172$23762c90$a401280a@phx.gbl...
> I am performing a lookup function but in certain
> situations the cell that is looked at is blank.  Is there
> a way to avoid getting the #N/A as the result?
>
> thanks
>


0
jwilson (359)
1/2/2004 9:01:17 PM
Franh,

Go to a cell that has a "looked-up value" of #n/a.

Select the "Fx" button.

Move your cursor through the formula and watch where the 
sub-formulas break down to #N/A.

Most likely you will find that there is a miss-match in 
data.  Meaning 991000 (as text) is not the same as 991000 
is as a number even though it looks the same on the 
screen.

To answer your question more specifically, I nned more 
information.  

HTH
Dennis

>-----Original Message-----
>I am performing a lookup function but in certain 
>situations the cell that is looked at is blank.  Is 
there 
>a way to avoid getting the #N/A as the result?
>
>thanks
>
>.
>
0
1/2/2004 9:10:28 PM
Try the ISNA command
It would look like this
=ISNA(lookup(lookup value, array)=True," ",lookup(lookup 
value, array)
This should return a blank cell in place of your #N/A

>-----Original Message-----
>Franh,
>
>Go to a cell that has a "looked-up value" of #n/a.
>
>Select the "Fx" button.
>
>Move your cursor through the formula and watch where the 
>sub-formulas break down to #N/A.
>
>Most likely you will find that there is a miss-match in 
>data.  Meaning 991000 (as text) is not the same as 
991000 
>is as a number even though it looks the same on the 
>screen.
>
>To answer your question more specifically, I nned more 
>information.  
>
>HTH
>Dennis
>
>>-----Original Message-----
>>I am performing a lookup function but in certain 
>>situations the cell that is looked at is blank.  Is 
>there 
>>a way to avoid getting the #N/A as the result?
>>
>>thanks
>>
>>.
>>
>.
>
0
anonymous (74722)
1/16/2004 9:33:36 PM
Reply:

Similar Artilces:

Printer button #3
Does anyone know how to remove the printer button in publisher 98 ...

CRM 3.0 Report scheduling wizard
Hi, Can you bale to provide the user manual for CRM 3.0 Report scheduling wizard? When I schedule the report, it says scheduled snapshot and snapshot available. After that If i click on that snapshot it is taking the same amount of time, as If i run the original report. So I have a doubt whether im using the correct process to schedule reports. What I need is: 1) User manual for thsi tool on how to use 2) What is snapshot mean exactly? is it different from original report or its same as the final output of the report? Thx Anil V ...

Excel Locks Up #3
Jim Rech Wrote: > Here's a helpful article: > > http://support.microsoft.com/?id=280504 > > I might suggest looking first at the corrupt toolbar (XLB) file issue. > I've > found that to the problem fairly often. > > -- > Jim Rech > Excel MVP > "sunrise-marketi" <sunrise-marketi.1eukso@excelforum-nospam.com> wrot > in > message news:sunrise-marketi.1eukso@excelforum-nospam.com... > | > | I'm running Windows 98 and office 2000. All of a sudden, when I tr > to > | open excel, the computer locks up. I tried un-in...

virus #3
I'm getting the same message as Jay "A program is trying to access e-mail addresses you have stored in Outlook. Do you want to allow this? If this is unexpected,it may be a virus and you should choose NO." How do I get rid of this? I have scanned and found no virus. "Stan" <anonymous@discussions.microsoft.com> wrote in message news:<263401c41bb9$e8ffbb70$3a01280a@phx.gbl>... > I'm getting the same message as Jay > "A program is trying to access e-mail addresses you have > stored in Outlook. Do you want to allow this? If this is >...

dynamic sort macro across 3 linked sheets
Good evening -B3 thru M3 in sheets Row 3 in "Sheet1, Sheet1 (2), Sheet1 (3 contains symbols which are dynamic i.e. alphabetic and change in an one sheet or all sheets daily so that the alphabetic order is los almost daily but which order is critical to maintain order and sens when viewed. -what macro be devised to simply sort these changes across the thre linked sheets and keep good order- an array or 3-d referrence? - I giv up and so this request for help thanks -wrpalme -- wrpalme ----------------------------------------------------------------------- wrpalmer's Profile: http://...

Lookup #7
Hi, In sheet2 cell F14 I have the following formula: =LOOKUP($C$1,SHEET1$A$14:$A$58,SHEET1I14:I58) Instead of finding the correspondant value in colomn I, Is it possible to display the AVERAGE of the 2 cells I+J ? What changes should I make to the previous formula? Khalil Hi Khalil, Here's a User Defined Function (UDF) that does what you require. I added instructions in case you don't know (yet) how to implement a UDF -- Kind regards, Niek Otten Microsoft MVP - Excel ================================================ Pasting a User Defined Function (UDF) ...

V-lookup and format
Hey, I am making a dashboard for work and am using lots of v-lookups t access a spreadsheet with lots of departmental information. I have th v-lookups for the data all figured out, my question is this: Is there a easy way to bring the format with the data. for instance some of th numbers are just numbers, others are percentages, thus they come to th new cell as .23 instead of 23%. Below is an example of the v-lookup i a using. =VLOOKUP($B22,'[People Hub.xls]people hub'!$E$9:$R$5000,'[Peopl Hub.xls]people hub'!F$5,FALSE) I don't want to preset the cells format in the da...

Outlook 2003 freezing #3
Single user running XP Pro with all updates, 2 gigs of RAM, RAID 1. A new install of Office XP Pro. I imported a fairly large .pst from my previous Outlook 2000/XP Home machine. That seemed to go fine. It hangs on the inbox...sometimes on the first message, sometimes on the 3rd or 4th. Then it goes into "not responding mode" and sometimes will X close, sometimes not, requiring task manager to close. It leaves an icon in the system tray, and will not restart without a reboot. I have run scanpst.exe, run the repair function from control panel>remove, done a little voodoo ...

Formula help- Lookup table
I am trying to create a lookup table for a calculator I am creating in excel. I need the calculator to look up a deration factor based on both the altitude and the temperature. For instance, if I put in a temperature that falls in the range of 50 to 60 degrees fahrenheit and a altitude falls in the range of 0 to 1000 feet, I need excel to tell me that my deration factor is 1.00. However, if I put in a temperature that is above 130 with an altitude of 3000 to 4000 feet I need Excel to tell me that my deration is 0.98. Is there a method or formula that would allow me to accompl...

Global Address List Issues. #3
Here we go again. Here is a good question. I have a contacts list owned by me. The name is a company name, not a person's name. Outlook is seperating my names last name first, first name last. This is not good because as I stated, these are company names, not proper names. Any and all help would be, once again, greatly appreciated. By the way, Sue Mosher is an Outlook goddess. Thank you Sue. -- darylakagod I can't follow this at all. A contacts list implies an Outlook contacts folder. What does that have to do with the Global Address List as the title of your message i...

Help!!!! Can't right click. #3
yep...I can click in any other program...just isolated to excel. I'm thinking it has to do with code in a macro that I ran at once point and now I can seem to find the macro/code/anything to disable it. -- jersey1000 ------------------------------------------------------------------------ jersey1000's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=16481 View this thread: http://www.excelforum.com/showthread.php?threadid=278316 ...

Pivot Table refresh #3
Data has been removed from the worksheet that feeds the Pivot Table. After Refesh the deleted field name still appears in the drop down list. How can the field name be removed? Unless you want to write a macro, delete the pivot table and create a new one. -- Regards, Tom Ogilvy "neeses" <neeses@discussions.microsoft.com> wrote in message news:C931DD0D-DDA6-4DA5-B5B8-72AF328BC8AD@microsoft.com... > Data has been removed from the worksheet that feeds the Pivot Table. After > Refesh the deleted field name still appears in the drop down list. How can > the field n...

Microsoft Query #3
I am trying to create a query in Microsoft Query that gets data from an Access Database. I click on New Query, which brings up a box for me to Choose Data Source. I select MS Access Database, then I select the the actual database. Next, a box titled Add Tables comes up, but there is nothing to select. I have done this successfully in the past, but I cannot figure out what is wrong. ...

Layaways #3
I post a Layaway for a customer requireing no down payment, then go to recall a layaway, make a payment against the layaway. Problem, when you go to the customer who made the purchase and look at account history or balance, nothing appears. The money and transaction appears under recall a layaway but not in the customers information page. Am I doing something incorrect and how do we track such transactions?? Correct me if I'm wrong but the customer information page tracks transaction that have occurred. I believe the actual transaction for layaways occurs when the final depo...

CRM 3.0 Reports Parameter Passing
Hello All, I'm trying to create a report that looks up values based on the AccountID. How do I pass the accountID into the report? Do I create a parameter named 'CRM_AccocuntID?' Thanks, Caleb Skinner Ascentium ...

hi #3
hi all. i am a new bee to vc++ while iam building a c++ file with socket .h header file iam getting linking error. plz help me out gs wrote: > hi all. > i am a new bee to vc++ > while iam building a c++ file with socket .h header file iam getting > linking error. > plz help me out Did you forget add "Ws2_32.lib" as Additional Dependence? thanks 4 ur reply but Ws2_32.lib filr is there in lib folder of vc.do i have to include this file. also tell me how to add these lib files in a project. Hooyoo wrote: > gs wrote: > > hi all. > > i am a new bee to...

error message #3
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel whenever I open any program in office I get this message "There is a problem with the dara base. Office might be unable to access the databaseor the data base file might be damaged. Without the data base you might not be able to use the address book and other features......personal information. To rebuild the data base open MS OFFICE/OFFICE then open MS DATA BASE UTILITY. For more info open the DATABASE UTILITY APPLICATION, and on HELP MENU, click the DATABASE UTILITY HELP. I do not know how to find. where to find an...

OE Attachments #3
My OE seems to repeatedly screen out attachments from my email messages. I don't seem to know why or how this is happening. Does anyone have any ideas? mikeandpat@sympatico.ca <anonymous@discussions.microsoft.com> wrote: > My OE seems to repeatedly screen out attachments from my > email messages. I don't seem to know why or how this is > happening. Does anyone have any ideas? Ask in an Outlook Express newsgroup. (Hint: Tools>Options>Security) -- Brian Tillman ...

AutoReply #3
How can I setup an AutoReply message to all messages received, WITHOUT the Exchange Server appending "Out of Office" to the subject line? I want to tell the sender that they have sent to an unattended mailbox, without Exchange telling them that the account is "Out of Office" Thanks! You are treading on dangerous ground here. Any autoreply rule such as that will open your server up to potential mail loops. You don't want to get into that situation. If you are truly intent on doing this and understand the risks, then you will have to enable automatic replies t...

Inserting row takes 1
I'm working with an Excel 2003 workbook with six worksheets (that I've opened in Excel 2007), one per year. There are only 14 columns with a very simple SUM formula in column 14. This is a spreadsheet that keeps track of auction sales by week with a grand total at the bottom of each yearly worksheet. Pretty simple. Since I've opened this workbook in Excel 2007, it is taking from 1 to 3 minutes to insert a blank row in the middle of other blank rows. This would be instantaneous with the same workbook in Excel 2003. I'm not a programmer, just a user and this is ver...

Microsoft warning about inability to operate any of its applications #3
Version: v.X Operating System: Mac OS X 10.4 (Tiger) Processor: Power PC A warning message comes up on the screen for any application, it happened whilst preparing a pp presentation with photos, which kept hanging up.....I need this for a presentation on Monday (Typical!) The application "PowerPoint" could not be launched because of a shared library error: "6&lt;Microsoft PowerPoint&gt;&lt;CarbonLib&gt;&lt;CFMPriv_ColorSync&gt;&lt;&gt;" Plse Help if you recognize this error, Carl It sounds more like an OS error - try running Disk Utility...

Microsoft CRM 3.0 Works Fine But...
I am trying to find a SQL person to help with a problem. Somehow, and I am not exactly sure how it happened, the "sysft_ftcat_documentindex" SQL file for my MSCRM data base got separated from the other SQL data base and log files and was placed on a separate drive which failed and had to be replaced. I did not have a backup of that file. As I was saying, Microsoft CRM works fine but I cannot do a full SQL database backup because that file does not exist. I can do a partial data base backup but I would like to do a full data base backup. Also, everytime the server is reboote...

Exadmin #3
On our 2000 server the exadmin is giving an error: "The system cannot find the path specified" and the path is set to \\.\BackOfficeStorage I'm not sure how to fix it or were to look. Thanks Giving the error where? In IIS Admin? Are you meaning that there is a red stop error on the Exadmin virtual directory? Does OWA work correctly? What happens if you stop and start the Default Web Site? Does the error go away? Are there any errors in the Application log? -- Ben Winzenz Exchange MVP MessageOne "Eric" <Eric@discussions.microsoft.com> wrote in messag...

Macro Help #3
I turn to you guru's for help with a macro question. I have to worksheets in my workbook. Both have a coulmn that has a 6 digit number in each cell (an invoice number) What I need to do is have a macro that starts with the first invoice # in sheet 1, searches for it in sheet 2. If it finds it, delete that row from Sheet 2. Then it will look at the 2nd invoice number on sheet 1, Search from it in Sheet 2 If it finds it, delete that row from Sheet 2. etc, etc, etc so, my question to you is..... How? Modify this to suit. Sub Deletematches() On Error Resume Next For Each cel In [c14:...

Rules Don't Work #3
I am using Windows XP and Outlook 2003 and have a problem with Rules. I have established 6 rules to move email to different folders when they are received. From day to day the rules turn themselves off in a random pattern. The problem is complicated because I move my pst file from one stand alone computer at my office to my stand alone pc at home. I have made sure that the "for this machine only" choice is disabled. I want the same rules to apply to both machines all the time. What am I doing wrong? Any suggestions would be appreciated. Bob Kufrin rlkufrin@sisterbay.com ru...