Show a blank result in a cell when there is no value in the "Lookup" cell

I apologize if this question has been asked, but I have been unable to find 
an answer searching the topics.

I'm using the following formula in cell C3:

=INDEX(LastName,MATCH(A3,EmpID,0))

When I type in an employee ID in A3, his/her last name shows in C3. 
However, when there is no value in A3, C3 shows error "#N/A".

Is there a way to show a blank cell in C3 until a value is entered into A3?

Thanks in advance!

Mike 

0
mslater518 (22)
8/5/2007 5:45:37 PM
excel.newusers 15348 articles. 2 followers. Follow

2 Replies
1496 Views

Similar Articles

[PageSpeed] 33

On Aug 5, 10:45 am, "Michael Slater" <mslater...@comcast.net> wrote:

> I'm using the following formula in cell C3:
> =INDEX(LastName,MATCH(A3,EmpID,0))
>
> When I type in an employee ID in A3, his/her last name shows in C3.
> However, when there is no value in A3, C3 shows error "#N/A".
> Is there a way to show a blank cell in C3 until a value is entered into A3?

Try:

=IF(A3="", "", INDEX(LastName,MATCH(A3,EmpID,0))

0
joeu2004 (766)
8/5/2007 6:17:41 PM
Thank you,

That worked great!!

Mike


"joeu2004" <joeu2004@hotmail.com> wrote in message 
news:1186337861.047999.222640@g12g2000prg.googlegroups.com...
> On Aug 5, 10:45 am, "Michael Slater" <mslater...@comcast.net> wrote:
>
>> I'm using the following formula in cell C3:
>> =INDEX(LastName,MATCH(A3,EmpID,0))
>>
>> When I type in an employee ID in A3, his/her last name shows in C3.
>> However, when there is no value in A3, C3 shows error "#N/A".
>> Is there a way to show a blank cell in C3 until a value is entered into 
>> A3?
>
> Try:
>
> =IF(A3="", "", INDEX(LastName,MATCH(A3,EmpID,0))
> 

0
mslater518 (22)
8/5/2007 7:08:20 PM
Reply:

Similar Artilces:

Field lookup query question
Access 2003. I have two tables. Master and IssuePart. Each table has a report number (Master.OA) that ties all this together. I wanted to make the Master table perform a lookup by matching the report number in IssuePart and bringup a listbox. But the query won't cooperate. I can manually enter a report number and get the results I want, but as soon as I try to change the criteria to the Master report number field name, it chokes with syntaxs errors and such. Here's the SQL the Builder produces. SELECT IssueData.[Part Number], IssueData.[Serial Number] FROM IssueData WHERE ((ISS...

BP Req Mgmt Lookup should show additional columns
When doing a lookup I should be able to configure the columns that I would like to see visible on the lookup. For example, when looking up an item only item number and item description are visible fields. I would like to configure the lookup to show additional fields, like the vendor name. ...

Totalling seperate cells from a HLOOKUP function
is it possible to sum the product of 2 or more cells, 1 cell being the result of a HLOOKUP function, without creating a third cell for individual results? Here is my problem: Row A contains a list of different locations Row B contains wether the delivery has failed or completed Row C contains the quantity of items on Invoice Is there a formula I can use to have the total quantity from all of the failed deliveries for that day? As the failed delivery locations may vary from day to day, I cannot just total those cells from each location I have tried this formula, but it re...

Lookup without empty cells.
Currently my formula is b6 =VLOOKUP($C$4,Functions!$A3:$K$175,2,FALSE). I need the above formula in cells b7:b30. I do not want the blanks to show up if the formula is false. c4 is a month number, and the list Im searching in has multiple entries for the same month. I would like to show all entries for that month in cells b7:b30 and not have to go to b182. Can this be done? -- comotoman ------------------------------------------------------------------------ comotoman's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27292 View this thread: http://www.excelforum...

lookup with 2 criteria #2
=INDEX(Gaps!$C$9:$C$2103,MATCH(1,(Gaps!$A$9:$A$2103=A2)*(Gaps!$D$9:$D$2103=F2),0)) works great for getting me the correct acct #. Thank you again Frank! But now I need to take it a step further... The data that I'm working with will eventually be imported into another database program where we already have some of this data. We do not want duplicate data so I need to delete out what has already been input to the database. On my main sheet, cell P2 contains the month # for that invoice. On Gaps page my columns look like this: A B C D E F G H I Site Fuel Acct Meter Feb'04 ...

Use personal.xls as lookup sheet?
Hi guys, Can anyone give me any comments against using the personal.xls sheet as a place to store lookup tables? I will not be distributing spreadsheets accessing these tables to anyone else. The tables will be income tax tables which will be references from other workbooks on my local hard drives. Thanks Frigid Digit If it will only be on that machine, can't see any problems. -- HTH Bob Phillips "lk" <lawrencekritzinger@hotmail.com> wrote in message news:dilpvk$4af$1@ctb-nnrp2.saix.net... > Hi guys, > > Can anyone give me any comments against usin...

complex lookup
I have a lookup table that I need to reference. based on a 3 digit # such as "2.22", I need to be able to find the cross section on the lookup table. Based on numbers across the top and down the left side; If I have the number 2.22, it will return for me .15 from the table lookup table (made up values) .00 .01 .02 .03 .04 ... .09 2.0 .31 .32 .33 .34 .35 ... .50 2.1 .21 .35 .51 .51 .25 ... .85 2.2 .53 .52 .15 .52 .51 ... .81 2.3 .89 .58 .23 .45 .15 ... .15 -- Thank you! Doug wrote: > I have a lookup table that I need to refe...

Why Does My Form Copy Values From My Lookup Box To The Other Recor
Hi - I am using Access 2003, and am not very familiar with Access or its terminology. I am not at all familiar with VB. I created a form from a query, and everything is generally working fine. One of the fields on the form is based on a lookup table for location. I have assigned a default value of 1 (which translates to NOT CHOSEN), and that's how everything shows up when I go into the form. I assigned the default both in the originating table and in the box on the form itself. However, when I change the first record to a different value (say, 2 which translates to QUEE...

Vlookup And Lookup Functions #2
Myrna, Thanks very much -- that is exactly what I was looking for. I believe you left out a '(' immediately before the 'MATCH'. This 'strategy' will work for a column LOOKUP and for an arra VLOOKUP. Thanks again, Chuckles123 : -- Chuckles12 ----------------------------------------------------------------------- Chuckles123's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1494 View this thread: http://www.excelforum.com/showthread.php?threadid=27806 Not exactly. I see an EXTRA right parenthesis at the end of the MATCH function, before t...

Auto Increment Lookup codes
I am curious if there is a way to customize data entry so that lookup codes are randomly (and obivously uniquely) generated for us. It could be as simple as creating GUIDS or using the row ID for the lookup code. I could use a bit of guidance on how to customize data entry in such a way. ...

Vlookup Values
I have the following function in a worksheet. =VLOOKUP(J3,TABLE!$A$1:$B$54,2,FALSE) Typing in a value of in one cell of 11/8 returns a value of 8.00 in another cell which is fine. With the above VLOOKUP there are about 55 values. However, I need to create another VLOOKUP with values from 0.1 to 1000 which represents a lot of values. Would it be possible to 'band' the values, so that anything between 100 to 1000, returned a value of '1'? It would certainly cut down the entries. Could the second set of numbers be on the same worksheet, but in different cells in order t...

Interpolation/extrapolation and lookup problem
Hello, I'm trying to interpolate a value from an array, but must, given desired x, only interpolate between the nearest x-y pairs. I have ha little success with some functions I found online. My tables could either be listed in ascending or descending order depending on the given application. Attached below is an example of a table I would like to obtain dat from. I should be able to do the nesting once I sort the interpolatin and extrapolating out. Basically, what I would like to do is find the closest x-y values abov and below the x value requested in the input cell, then use the sl...

Combo Box lookup stops working.
I am attempting to to add some features to an exsisting db. My Main form displays a family last name. On a tabbed control there is a subform to add family members to the general last name. I created a family lookup combo box using the wizard. This control works fine. I also need to be able to search for a first name that could appear in the subform. I created a form (named LookupForm) that displays all of the first names from the bound table. When a first name is selected in the combo box there is a button labled "Go to first Name". The "On Click" even...

lookup or match?
on tab: Weeks NQCQ I have a table as follows Col A Col B Col C Week CQ Week NQ Week wk2 1Q10 Wk-12 Week 2 wk3 1Q10 Wk-11 Week 3 wk4 1Q10 Wk-10 Week 4 wk5 1Q10 Wk-9 Week 5 wk6 1Q10 Wk-8 Week 6 wk7 1Q10 Wk-7 Week 7 wk8 1Q10 Wk-6 Week 8 wk9 1Q10 Wk-5 Week 9 wk10 1Q10 Wk-4 Week 10 wk11 1Q10 Wk-3 Week 11 wk12 1Q10 Wk-2 Week 12 wk13 1Q10 Wk-1 Week 13 wk14 2Q10 Wk-13 Week 14 On tab "Week at a Glance", in cell B4 I will put in the current week, lets say I input wk8, on the same tab, I need a formula to look at the table ...

6 lookup fields in Opp linked to contacts
I want to create 6 lookup fields in the Opp form that allows to user to search all contacts. I have tested the 1 to many an many to 1 in the relationships area but for some reason it just not working for me. Can anyone point me to helpful links or explain how this can be done.. Thanks!! The opportunity screen uses the customerid attribute to refer to contacts OR accounts. Add this attribute to your opportunity form and you should have a lookup list for a contact. The database architecture of CRM only allows ONE contact per opportunity - you won't be able to put 6 customerid fields o...

Re: Using one of the LOOKUP functions
Hi everyone. I have done a search but not been able to find what I'm looking for so perhaps some kind person here may be able to give me some pointers. My dilemma is thus; I have a sheet with 11 columns of data in around 45 rows (the number of rows grows as more data get put in during the month) The first column contains people's names. At the end of the month I will sort all the data on the Name column. I then subtotal on these names and this data is contained within the 11th column. The data I want to find at the end of each month will be contained within the row that starts wit...

VLOOKUP formula appears in the cell I need to see the result in
I am using the VLOOKUP function but when I complete the formula, the formula just appears in the cell I need the result to show up in. Can anyone help? Hi it is possible that the cell has been formatted to text prior to the entry of the formula: click on the cell, choose format / cells - number tab and click on general and click ok. then you might need to press F2 then F9 and enter to get it to work or tools / options / view tab, untick formulas or ensure that your VLOOKUP formula has no space before the =VLOOKUP(........ -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm...

blank lookup tables after SP2
Hi all, I have just installed SP2, WSS3 SP2 first, then Office Server SP2 updates. Now, if I go to the Custom Fields and Lookup tables, I cannot edit either? Enterprise Custom Fields show "New Custom Field" and Lookup Tables show a blank page with "New Lookup Table" at top. I am not able to install Web access components since SP2 either. Should I try restore my pre SP2 outline codes? Thanks Hi Roger, if memory serves me correctly there was a bug regarding Outline Codes, but I think it was to do with the client? Have you put SP2 on the client too? Also, bef...

Restricting Customer Lookup to Accounts (in 4.0)
This handy (unsupported) opportunity form onLoad event script worked in CRM 3.0 but seems to be useless in 4.0 //Allow only accounts to be selected crmForm.all.customerid.setAttribute("lookuptypes", "1"); Any ideas? ...

Adding lookup fields
Can someone help with how I add a field of type Lookup to an Object/Form in CRM? Many thanks, Hi, Adding a lookup field means making a relationship with the other entity and than just adding the relationship attribute on form. Following URL will explain you in details. http://www.microsoft.com/dynamics/crm/using/customizing/addlookup.mspx -- PLEASE do click on Yes or No button if this post was helpful or not for our feedback. uMar Khan Email for direct contact: imumar at gmail dot com "Mark Davies" wrote: > Can someone help with how I add a field of type Lookup to an Ob...

Lookup Filter
I've seen similar questions posted here, but so far no answers - but hey, I'm hopeful someone will be able to assist! On the opportunity form, there is a field where you can do a lookup to either contact or account - in our company, however, we always want to populate the account in this field. Then, after users selected the account, we want them to be able to select a contact *associated with the chosen account*. So far we have been able to create a 'fake' lookup field that will allow them to select a contact - but we haven't been able to restrict it to only the ...

Table-lookup transformations
Hi, we are doing some table-lookup transformations based on LEFT and INNER joins. Probably around 100 - 50K rows/second or something like that. I have noticed that the lookups are getting slower and slower due to a growing database. The server itself is heavily loaded, CPU and IO-subsystem - which I know is not a good combination with SQL server :| I am looking for a way to increase the table-lookup transformation speed without mixing up things with SSIS.. Are there any good ways of doing table-lookup transformations within SQL server? Would it be a good idea to write s...

Howto use excel cell value to lookup an oracle table
I have an excel worksheet containing a list of userid's in cell A, for each row I want to reference the userid in cell A and connect to an external Oracle database to lookup and return further details for that user. eg.. their department and location etc.. Can you advise how I do this. I am currently using Excel 2003 ...

reverse DNS lookups
When mail is being sent to a server that requires reverse DNS, does the reverse DNS entry have to match the DNS lookup or can they be different? Ex DNS resolves companyA.com to 1.2.3.4. The MX record for companyA.com is 1.2.3.5 (mail.companyA.com) Can the PTR for 1.2.3.4 be set to mail.companyA.com and mail servers that require reverse DNS still deliver the mail? Or must the PTR for 1.2.3.4 be set to companyA.com ? Essentially, do mail servers doing reverse DNS lookups only look for ANY reverse DNS, or do they look for a reverse DNS that matches the forward DNS, in order to decide if it ...

Data Validation Lookup List from Another File
Hi, Without duplicating the cell contents in the current spreadsheet (call it Template), is there anyway to have a dropdown list of items (starting in cell T38 and going down to T45) from another file ([Price_List.xls]License_Only!C2 to C whatever row which will change). Then, when the item is selected from the list in T38, lookup the cell to the left in row D of Price_List.xls and put it in S38. The reason I don't want to duplicate the cell contents in the current spreadsheet is because the file will be way too large to deal with. I hope that explains it well enough Thanks for any he...