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

Similar Articles

[PageSpeed] 44

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:

analytical accounting lookups should only show valid codes
When entering AA transaction distributions, the code lookup window should only show valid code combinations. It's not intuitive for the user to have to select a code and get an error message to discover which codes are valid. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the mess...

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 On Aug 5, 10:45 am, "Michael Slater" <mslater...@comcast.net> wrote: > I'm using the following formula in cell C3: > =INDEX(LastNam...

Form w/ combo box from query based on lookup field not alphabetizi
I have a query that includes a lookup field (combo box). I included an ORDER BY clause in the query for the lookup field. The field sorts the lookup list alphabetically and in the query. I'm happy with that. I created a main form w/ a linked form based on the query. The linked form contains the lookup field. However, the lookup table is still not alphabetised. Why? Please help in alphabetising the field on the linked form. All help is prematurely appreciated. -- Thanks, Karen Table sorting is VERY strange - I think Bill Gates understands it and no one else without a MVP...

lookup text within string of text???
I need to look up "certain critera" within a string of characters, then return that "certain criterea" to a new column. Some examples of a strings of characters may look like these: K5J091509001 Sample PO#S881009 K55sample PO CarrieRJR TJ5 My "Certain Critera" I have listed on another sheet, named "REP ID" K5J S88 K55 RJR TJ5 How do I pull out the 3 characters of "Certain Criterea" from the string of text and copy or enter it into a new column? It's never consistant. This is the problem I'm having! Sales Reps enter...

Set lookup in JavaScript possible?
Hi, I wonder if it is possible to set a lookup using javascript. According the sdk, I should set the id, the name and the typename which I do. However, nothing is displayed. Is it possible or is only possible from server side? Hi, Yes, it is possible and it is exactly like how it is mentioned in CRM SDK. //Create an array to set as the DataValue for the lookup control. var lookupData = new Array(); //Create an Object add to the array. var lookupItem= new Object(); //Set the id, typename, and name properties to the object. lookupItem.id = '{1AAC1363-01A1-DB11-8432-0003FF9CE217}'; l...

Messages awaiting Directory Lookup #4
Here is my issue. I am running Exchange 2003 and Exchange 2000 in seperate domains. Topology: TLD is my Root Domain in the Forest structure with Exchange 2003. I have 3 other 2000 domains all running exchange 2000 servers 1 in each domain. I can send and receive email from the outside just fine but I can't send mail internally from one of the 2000 exchange servers puts messages in the directory lookup queue. I have looked at the GC and added a few more. I do get an error 6004 for the message categorizer. this one 2000 was restructured into this new TLD forest. I did an active dire...

Lookup values
I have an excell spreadsheet that contains mailing address. each address is identified by a unique number. I wish to do a second mailing to the addresses that did not respond to my first mailing. I have listed the unique number in a seperate work book. I wish to use the lookup function to pull the address that do not match the inique number recived. any suggestion? Assume ALL IDs - Addresses are listed in Sheet1's cols A & B In Sheet2, In A2 down, you have listed the IDs for those > addresses that did not respond to my first mailing Put in B2: =VLOOKUP(A2,Sheet1!A:B,2,0)...

Dexterity Scrolling window Lookup button problem!
Hi, I am creating a new window with a scrolling window. There are four fields in the scrolling window. 'From Item No:' , 'U Of M', Qty and 'To Item Number'. The 'From Item No:' and ' To Item No:' fields have lookup buttons linked. PROBLEM FACED: I have two lookups one for 'From Item No:' and one for 'To Item No:'. I am using 2 different local fields in the scrolling window to store the value returned by lookup buttons. When the user selects a 'from item no:' using lookup button. It fills the From Item No: in scrolling ...

Access 2007 not showing field names in query wizard/lookup wizard/etc
Hi, This happened to me while at a customer site and caused a few problems. It is no longer occurring, but I wonder if anyone has suggestions. The issue was that on new databases or existing ones, i could not create new queries or use the lookup wizard when creating tables because the list of fields in existing tables or queries would not show up. Today i go into the exact same db's and it works fine. It's not a huge issue because i could just do all the queries by hand and whatnot, but it slowed me down considerably and i just want to try to avoid it in the future (plus i just th...

I need a VLOOKUP to display 0 or blank instead of N/A
=IF(ISNA(MATCH(LookupValue,LookupColumn,0)),"",VLOOKUP(YourFormula) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Rachel" <Rachel@discussions.microsoft.com> wrote in message news:E7AB5081-3A2D-4B6F-B22B-900057DD3AE7@microsoft.com... Rachel =IF(ISNA(your lookup),0,your lookup) or =IF(ISNA(your lookup),"",your lookup) Regards Trevor "Rachel" <Rachel@discussions.microsoft.com> wrote in message news:E7A...

Adding third lookup value to index/match
I understand how Index/Match works with double lookups... But I cant quite get the third lookup down. Looking to add a third lookup value and range to this formula (range z) =INDEX(table, MATCH(x,range x,0), MATCH(y,range y,1)) Data is laid out like so... (Vertical column range is x) A B C D E F G H 1 range yyyyyyyyyyyyyy -> 2 range zzzzzzzzzzzzzz -> x x x x x x any suggestions...? How does the 3rd range relate? Need more specific details. -- Biff Microsoft Excel MVP "J.W. Aldridge" <jeremy.w.aldridge@gmail.com> wrote in mess...

Lookup value off by one row
Hi, Any help you can provide on this would be appreciated. I have a worksheet that identifies how far a number is from target. I created the lookup below to coorespond to the productivity increase for next year. =LOOKUP(C4,LookUpValues!$A$1:$A$402,LookUpValues!$B$1:$B$402) C4 is -10.9, in the lookupValues tab -10.9 is in row 93 column A as is the desired result return of 5.6 in column b, however the lookup function is returning 5.7. The issue may be that the data and the lookupValues tab numbers are not exactly the same. -10.90001 and -10.9004 may display the same, but w...

Display in Lookups
Does anyone remember in the Dynamics Table Import how to set the field "Display in Lookups" in the GL00100 so that all series are highlighted? It was something like filling in the field as a constant with all fffffffffff's. I tried serveral different letters but upon importing the "Display in Lookups" had nothing highlighted. I also tried using SQL Query Analyzer to set the field to "OxFFFFFFFF" like the rest of the accounts. Unfortunately the field is not a string. You set the field to 65536 . -- Richard L. Whaley Author / Consultant / MVP 2006-20...

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

Vlookup for negative values?
Is it possible to have a VLOOKUP that lookis in a range for the first instance where the value goes negative? I have an economic table and I want to have a summary cell indicate the value of a corresponding column cell when a certain column goes negative. Hi one way: =INDEX(A1:B100,MATCH(TRUE,(A1:A100)<0,0),2) entered as array formula (CTRL+SHIFT+ENTER) will return the value form column B for the first instance of a negative value in column A HTH Frank FeDude wrote: > Is it possible to have a VLOOKUP that lookis in a range for the first > instance where the value goes negative? &...

lookup type metadata lost when usining Send To option
When I move a document between folders (even in the same library) any lookup type metadata gets lost. I have already reported this problem in a another post, a couple of weeks ago, but still have no solutions yet. I have tested the Send To option on three different WSS 3.0 and MOSS 2007 farms and I get the same result. I can't find any post reporting such issue. Has anyone succeeded in using Sharepoint' Send To option to move documents with custom content type, having lookup type metadata, whitout losing metadata values? When moving items inside of SharePo...

Lookup Values
Hello all, In crm 3.0, we can choose different fields in different entities for some lookup objects. For example, we can choose either contact or account for parent customer field in contact form. In the same way, for recipient field in a phone call activity, we can choose lead, contact, user or account. My problem is that i dont want to see some entities for lookup objects. For example, i just want to choose contact or lead records for recipient field, and i dont want to see the others(account or user). How can i do this? Thanks for all suggestions. Michael Hoehne has a script you can add...

Custom field with lookup table
Hi, I'm trying to use a custom field with a lookup table. The problem is that I can't save my lookup table. Always getting an unknown error. My lookup table is built like this X.Y.Z where X is "1 to 6", Y is "A to Z" and Z is "A to Z + 1 to 199". Total of 35262 lines. So I wnat to know if there's a limit to the lookup table size ? Thanks, Jici My guess would be that you are trying to cut and paste those 32,000 lines into the web IU? That's as likely to be the problem as the total number, I find issues cutting and pasting f...

lookup options
hello, i am a hombuilder and i have begun creating a workbook to streamlin some paperwork operations. the workbook has three sheets (fil attached). the sheet named 'options' has several checkboxes fo different options. i would like to create a formula that searchs for all checked option and inserts them in the 'start order' worksheet. please take a look at the attached file and any help would be greatl appreciated. thanks aladdi Attachment filename: start order.xls Download attachment: http://www.excelforum.com/attachment.php?postid=45700 -- ...

vlookup and match first value greater than
I need to lookup a product id and return the date of the first value greater than zero For instance a b c d e f g 1 Date 4/12 4/19 4/26 5/3 5/10 5/17 2 Product A 0 11 35 0 0 125 3 Product B 35 50 75 100 25 36 If I lookup Product A in the spreadsheet and want to get results from columns e through g I would want to see 5/17 as the result. I have this formula which works against a fixed row reference but have been unable to incorporate a lookup value into this to return the same result. INDEX($e$1...

V-lookup and Countif
I'm trying to create a formula that will look up criteria in one column and count the result of another column. Here is some data as an example: Fruit Have in Stock Orange X Pear X Apple Orange X Orange Apple X I'm trying to create a formula that looks up "Orange" in column A and then counts the number of "X" in column b to return a results. So my result for Oranges should be 2 because out of the 3 listed only 2 have an "X" marked next to it. Hi, Try this =SUMPRODUCT((A1:A10="Orange")*...

Lookup remit to address on check history
Is there anyway to get the address the check was mailed to on the Payables Payments Zoom window? i.e. vendor calls because has not received payment and ask where check was mailed. If Remit To on vendor card has changed since check was written there is no way without having a copy of the check to tell to what address the check was sent. ...

V Lookup and Multiple Values
Hello, I am looking to figure out how to use V Lookup with multiple values. I have about 30 of the same name in column A (This is picked out of about 30.000 names in column A). How can I do this? Thanks. You can use a helper column with a COUNTIF formula to turn those 30 multiple values into 30 unique values, by tagging a sequential number onto the value. Then you can use an INDEX/MATCH combination to bring the matching data across, either singly or for all values. I could give you more details on how to do this if you could supply more details of what data you have, how it...

Lookup multiple values
On one worksheet, I have a "table" with a header column & header row, along with data. ON another worksheet, I would like all of the data in the first worksheet to be in a list; the applicable column header would be placed into one column, the row header into another one. Example: Table on first worksheet: A X P 1 2100 2101 2102 2 2103 2104 2105 3 2106 2107 2108 Desired results on 2nd worksheet: A B C 2100 1 A 2101 1 X 2102 1 P 2103 2 A 2104 2 X 2105 3 P ...

Issues Creating a Lookup Field
I have created a new entity called "SW License". On this form, I have a field called "new_licensenumber" which needs to be a lookup field. When I attempted to create a relationship definition of type "Many-to-1 Relationship, "contract line" is not available to me in the Primary Entity list. What do I need to do to make the "new_licensenumber" a lookup field? The contract line (contractdetail) entity does not support new relationships. If you open it and view the relationships, you won't find the button to add a new one. Maybe you can use ...