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
1573 Views

Similar Articles

[PageSpeed] 32

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:

Auto Complete (auto fill) in Lookup Fields
Main table looks up to 2nd table, using this code: SELECT [1a-Emb Format-tbl_Formats].[ID], [1a-Emb Format-tbl_Formats].[Format] FROM [1a-Emb Format-tbl_Formats] ORDER BY [Format]; In form view, when typing a value from the list, the value "auto completes" fine until I close the database, re-open, add a new record and try to populate the field again. Even if i type the copy exact value from the lookup table list, an error results saying it doesn't match. I'm forced to click and scroll on the drop arrow to populate. When I remove the relationship from the ...

Problem in creating a lookup field
I am creating a new entity "Decision Makers", in which Decision Maker is from Contact. for that, i have created 1-to-many relationship for Decision Maker field, but not able to get that field in the list of attributes while placing attributes on the "Decision Makers" form. Is it like that if we create 1-to-many relationship, then it does not show in the attribute list? if yes, then what is the alternatives? or it is possible to go for many-to-1 relationship... any one can please help me out.. ...

in word 2007 how do I turn on the dictionary lookup feature
when I use spell check and correct my mistake if I try to use the dictionary lookup feature it is not responding. How do I activate this feature? To look up words in Word 2007, you can click the Research button on the Review tab. -- Stefan Blom Microsoft Word MVP "Dino" wrote: > when I use spell check and correct my mistake if I try to use the dictionary > lookup feature it is not responding. How do I activate this feature? ...

displaying lookup fields
Can anyone help. I have created two tables customers and booking. From the booking table I have created a form for entering the booking information. For the customer field within this form I have used lookup columns from my customer table. In the booking form I click on the customer field and it correctly shows list of customers. I select the correct customer but the field only displays the first name. Is there a way I can select the customer and the filed to display both the first name and last name, address and postcode? I am desparate can you help? Firstly, I hope you are not usin...

Lookups and Offsets
I would like to take a previous issue to the next level of complexity. Here's the scenario -- Message posted from http://www.ExcelForum.com Here's a possible solution :-) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "wmjenner >" <<wmjenner.110p7g@excelforum-no...

Lookup Issue (V 3.0 CRM)
Hello, On my Opportunity Form, I have a look-up field called "customerid". I need to write some JavScript code that when the user selects a customer (account) from the customerid field lookup, the custom field CFScompanyname is automatically fills in. How do I get the company name from the above LookUp Does anyone have an idea on how I would go about doing this? Thanks In the onChange event of the customerid field, put a javascript like: var custName=crmForm.all.customerid.DataValue[0].name; { crmForm.CFScompanyname.value=custName; } -- Matt Wittemann, CRM MVP http:/...

Multiple VLookup Values
Hello All, I am wondering if someone can help me with a custom Macro using the VLOOKUP logic. I need to do something like: VLOOKUP(lookup_value1,lookup_value2,table_array,col_index_num,range_lookup) Where lookup_value1 AND lookup_value2 must be there for the item in col_index to be returned. So I guess I would need to let the macro which column lookup_value1 and lookup_value2 should test in? Does this make sense? Alex Yep. But you don't even need a macro to do this... You can use this kind of syntax: =index(othersheet!$c$1:$c$100, match(1,(a2=othersheet!$a$1:$a$100)*(b2=othe...

vlookup for words content when there are duplicate values
Hi, Can somebody help me here. Thanks in advance. When I vlookup Material 1 to Material 2 for the Project No, I get the vlookup Project No. as 222-56 for all the duplicate Material values. How formula to use so that when there are duplicate values in Material2, I want to have vlookup searching to match Project No. containg "AA" as 1st choice ? (if no Project No. containing "AA", then looks for its content "AB" and subsequently looks for "AC" if there is no "AB"). ColumnA ColumnB ColumnC Material 2 Duplicate? Project No. 224410 duplica...

to lookup data for 'today()'
e.g A B C D 1 Date (From) 01 Jan 2009 01 Feb 2009 01 Mar 2009 2 Date (To) 31 Jan 2009 28 Feb 2009 31 Mar 2009 3 Actual Qty 205 217 300 4 Target Qty 180 250 401 if today = 15 Feb 2009, how to get actual qty using excel formula? Hi, =SUMPRODUCT(--(TODAY()>=A1:H1),--(TODAY()<=A2:H2),A3:H3) "nordiyu" wrote: > e.g > >...

Lookup problem in Excel 2003
I had a formula in an Excel workbook called Payments to update the bank balance during 2011-2012: =LOOKUP(1E+100,'\\Pfadc01\company data\ADMINISTRATION\Excel\Accounts 2011-2012\Bank 11-12\[2011-2012 Bank balance.xls]PF'!$F:$F) This worked perfectly well until I needed a similar formula for 2012-2013. I have amended it as appropriate but it only returns 0.00 on my Payments sheet. This is the formula I am using: =LOOKUP(1E+100,'\\Pfadc01\company data\ADMINISTRATION\Excel\Accounts 2012-2013\Bank 12-13\[2012-2013 Bank balance.xls]PF'!$F:$F) Can anyone see where I have gone wron...

copying value from lookup into textfield
Hi, I found some posts to this question here in the forum and some in the internet, but most of them belong to CRM 3.0 and some use methods I not allowed or unable to do |-) So my questions are: Are there any Addons for this? Is there a method, just using javascript within the webclient? (I am not able to programm any callout or webservice) best regards Antonio It's an easy script. Assume your fields are named new_text and new_lookup, then use the following: crmForm.all.new_text.DataValue = crmForm.all.new_lookup.DataValue ? crmForm.all.new_lookup.DataValue[0].name : null; Or in ...

Lookup function help please
I'm trying to use the following LookUp function but it keeps on coming up as #N/A for option 1 but seems to work for 2,3 and 4, can someone tell me where I'm going wrong =LOOKUP(AU17,{1,2,3,4},{".175","0","0","0.05"}) The AU17 cell has the following formula in it, and it works - this is for VAT =IF(VC1="U","1", IF(VC1="Z",2,IF(VC1="E",3, IF(VC1="P",4,)))) Many Thanks for your help Remove the quotations marks around the 1 in AU17's formula: =IF(VC1="U",1, IF(VC1="...

Lookup column
Hello Everyone, I have a lookup column in my database with two parts. One part lists the supplier's name with the supplier's ID number directly accross in the other part. i enter data via a form. What I would like to do is select the supplier field on the form but have the supplier's ID number auto populate in another field on the form. Is this possible or am I going about this the wrong way? Thanks. If by "lookup column" you mean a combo box, and if both the supplier name and supplier ID appear in the combo box list, then you can use the column method to accomp...

Supressing Zeros from null lookups
I am filling a 'Top Ten' table with data from another spreadsheet, however when there is no contents in the linked table, my report shows 0. I would have expected the default behaviour to leave the cell blank. Is there anything short of an if statement to fix this? A preference I am overlooking? That's how Excel treats empty cells - you need to do something like: =3DIF(your_formula=3D"","",your_formula) Hope this helps. Pete On Aug 8, 8:21=A0am, Gaijintendo <Gaijinte...@discussions.microsoft.com> wrote: > I am filling a 'Top Ten' table w...

Lookup function Question HELP
This is a multi-part message in MIME format. ------=_NextPart_000_0032_01C4B158.8F36ACE0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I'm trying to perform a lookup function =3DLOOKUP(H8,sheet2!A:A,sheet2!C:C) This formula only returns the first item of a list of items matching the number in A:A (there could be more than 10 under the catalogue number = in A:A. I've made a list in B:B (1,2,3 etc.) for each of the catalogue = numbers in A:A How would I augment B:B into the formula to coincide with the number in = the "B...

Lookup function for two variables and return another cell?
Hi! I am looking for some help. Seems pretty simple but I can't seem to figure it out. I know I'll need a lookup function and an "AND" function of some sort... Take a look and let me know what you think. row/column A B C D 1 New SSN 1st signed Blue 2 2 New SSN Calc - not signed Green 1 3 Previous SSN 1st signed Red 3 So I need help finding the combination of "New ...

Data validation lookup
Is there a way to have a drop down list using data validation in file "jobs.xls" sheet "Sheet 1" Cell C4 to select a contact from a list that is in the file "parts.xls" sheet "contact" and fill in the corresponding cells in "Jobs.xls". The list of contacts starts on row 3 and goes to row 48 now but there will be more in the future. If there is another way to do this other than data validation I am open to other ideas. In "Job.xls" Sheet name is "Sheet 1" C4 C5 C6 D6 E6 C7 D7 C8 C9 ...

Need help on LOOKUP function..please
I'm doing revision of my exam and I don't understand about Lookup function, what's this function for?? When I read the instruction aboout "Lookup" on my revised exam paper, I don't know what the formula I should type in, can you help me to add formula on this data.(About Gem company) Instruction: _\"In_the_main_table_in_the_cell_under_Cut_Cost,_enter_a_formula_using_Lookup._This_formula_looks_up_the_Cutting_Cost_in_the_Cutting_options_table_using_the_Gem_cut_and_divides_this_value_by_the_Carat.\"_ :eek: Please see the attached file for data details.:con...

Lookup table troubles in query
I am using a database, that I admit I created before I was the wiser, and used combo boxes (lookup fields via the Wizard) in the database table. The database is now filled with data. The table has multiple fields, but there are four fields from which I would like to extract data and combine into one field. Field one is a yes/no type and the other three consist of combo boxes. In building a SQL statement in the query builder, I am unable to create a statement that uses data in the combo box fields as a filter--as you would expect. It is unable to find the criteria that I specify in...

Need a lookup formula that matches 2 values and returns the 3rd va
I neeed to merge 16000 rows of data with some duplicate vales. Poor record keeping lead to two lists and each was updated independantly if at all. Some orders show up one list and not the other so I can't just sort or do a dup search. I'm trying to recreate this data and track the status of all orders for the past 12 months. When orders were shipped out in parts the same order number was used and this has been a nightmare trying to sort the duplicates and match the dates. I need a formula for F that returns the current status based on the order number and date. I tried a...

fill a particular cell on a series of worksheets from a lookup tab
In the first worksheet of my workbook I have a list of part numbers listed in a column, say approximately 20 part numbers. I would like to have one worksheet for each part number, with cell A1 containing the part number for that worksheet. So if my list was: pn1 pn37 pn469 pn2034 then cell A1 of the first worksheet in the series would ="pn1", cell a1 in the second worksheet of the series would ="pn37" Is there an easy way to populate these worksheets from the list? Thanks, Rob Samples You could use a little macro: Option Explicit Sub testme() Dim testWks ...

lookup confusion
Hi -- Can someone help me with this formula? =INDEX(INDIRECT("'"&DealPlatformType&" Platform'!I90:I98"),MAX(1,MATCH(Planner!DealBlockSize,INDIRECT("'"&DealPlatformType&" Platform'!F90:F98"),1)))+VLOOKUP(Planner!DealBlockSize,INDIRECT("'"&DealPlatformType&" Platform'!F90:H98"),3)*(Planner!DealBlockSize-(VLOOKUP(Planner!DealBlockSize,INDIRECT("'"&DealPlatformType&" Platform'!F90:I98"),1))) I need it to only lookup a value now and not do do so much so...

Lookup record dialog box
The lookup dialog box field for different types of activities are different. For example, if we add job title in the find columns for Contact find (uder form and view). The job title is seachable in Phone activity Receipeint field, but it is not in To field of email. The two lookup foms also look different. Is there a way to make Job title searchable on Email lookup dialog box? Thanks ...

lookup record
is it possible to add more tabs into lookup records dialog: eg: create a quote -> add a product -> click the find icon then it pops up the lookup record dialog it only shows product name + product ID is it possible to change one of these columns or add new columns? ...

how do I setup a subcategory lookup based on category selected?
i'm setting up a table which has categories and subcategories. I want to do both with lookup listboxes in two seperate fields. I created another table with category and one of its subcategories for a two field table. There are records for all categories and their subcategories. I have the first lookup populated with just the categories. I'm having a problem generating a listbox with just the subcategories for the select category. Any help would be greatly appreciated. See: Limit content of combo/list boxes at: http://www.mvps.org/access/forms/frm0028.htm -- Allen Brown...