Returning a value if data is in a large list

I think you use a IF function but I am not sure.

I have two large lists of numbers.  The lists are in columns A and C with 
columns B and D blank.  In B, I want to insert an "X" if the number in column 
A next to it is somewhere in column C.  I tried =IF(A2=C:C,"X","") and 
=IF(A2=C2;C1000,"X","") but they do not work.  Is there a way I can do this?
0
ChrisRad (1)
6/21/2005 4:40:07 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
533 Views

Similar Articles

[PageSpeed] 23

One way:
=IF(COUNTIF(C:C,A1)>0,"x","")

Another way:
=IF(ISNUMBER(MATCH(A1,C:C,0)),"x","")

I like the second way.  It'll be faster for larger lists in column C.

ChrisRad wrote:
> 
> I think you use a IF function but I am not sure.
> 
> I have two large lists of numbers.  The lists are in columns A and C with
> columns B and D blank.  In B, I want to insert an "X" if the number in column
> A next to it is somewhere in column C.  I tried =IF(A2=C:C,"X","") and
> =IF(A2=C2;C1000,"X","") but they do not work.  Is there a way I can do this?

-- 

Dave Peterson
0
ec357201 (5290)
6/21/2005 5:47:43 PM
=IF(ISNA(MATCH(B4,D4:D8)),"","X")

is, I think, what you want.

Gaz


-- 
gazornenplat
------------------------------------------------------------------------
gazornenplat's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24494
View this thread: http://www.excelforum.com/showthread.php?threadid=380928

0
6/21/2005 5:55:34 PM
Reply:

Similar Artilces:

Publisher
I am not sure if this post belongs in this newsgroup or in Access. I have not recieved an anser in the Access group so I will try it here. I have a document designed in Publisher 2003 that I want to do a merge with a query in Access 2003. When I choose the Access front end on the local computer to link to and test the connection I get a "Microsoft Data Link Error" window that states "Test onection failed because of an error initializing provider. Cannot start you application. The workgroup information file is missing or opened exclusively by another user." The Acce...

Drop-Down List
How do I select from a drop-down list, and have a second drop-down list change based on my selection of the first drop-down list. hi see this site.... http://www.contextures.com/xlDataVal02.html regards FSt1 "Jarod" wrote: > How do I select from a drop-down list, and have a second drop-down list > change based on my selection of the first drop-down list. there are several ways to do this - - one way is say your drop down is A1 and your choices are Canada / USA. were ever you make a list for your second dropdown list = Ontario / Alberta / Quebec etc. in t...

Customer Returns
I'd like to know if there someone has written a RMS report that shows the customer account number, customer's name (last, first), and other customer statistics that INCLUDE pieces purchased and pieces returned. Or, if someone with more knowledge of SQL would care to share their coding expertise to accomplish the same, it would be appreciated. ...

Zero-value Budget Column in FRx SP11
Hello all, We have just resolved an interesting problem that one of our customers was encountering after upgrading from FRx SP9 to SP11, that I'd like to share with you all, along with our resolution. Bear in mind, I am not recommending this resolution to you, so much as providing it to assist you with your own enquiries. The symptoms of the problem are as follows: A report contains a two columns, a budget column and an actuals column. Running the report on FRx SP9 produces the correct figures. Running the report on FRx SP11 produces correct actuals, but the budget figu...

Chart Values and X axis values from VBA
Hi, I'm creating a series of charts based on data in many adjacent columns based on Name defined Ranges. However, I have to hardcode the Workbook and Sheet names that are used for the source names. My piece of code is: With ChtObj.Chart.SeriesCollection(jbl) .Values = "=Book3.xls!Sheet1_COL_" & ColAry(z) .XValues = "=Book3.xls!Sheet1_Date" End With I want the code to handle the workbook & sheet names more dynamically to accept a variable for wbk name or sheet name (or number). I can't get any syntax to work. Experts Help! Thank you. Kohai ...

List view with no item selection
How could i do that Handle LVN_ITEMCHANGING notification. Before return assign value 1 to *pResult. "Mubashir Khan" wrote: > How could i do that > > > ...

Return-Path: <> from Postmater
Hi, I got Exchange 2003 SP1 on Windows 2003 in Native Mode and when I receive bad mail the response is made from postmaster@mydomain.com but the return-path field is blank. With some anti-spam filter this Email is Junk. Return-Path: <> Anybody know how to specifeid a return-path to the NDR mail? _____________ Thank you Sebastien On Fri, 4 Nov 2005 12:48:04 -0800, "Sebas" <Sebas@discussions.microsoft.com> wrote: >Hi, > >I got Exchange 2003 SP1 on Windows 2003 in Native Mode and when I receive >bad mail the response is made from postmaster@mydomain.c...

Problem installing up sample data
CRM install went fine. I go to select the user who will run the sample data import wizard, and there isn't one listed. Should I create a new user in AD under Adventureworks & User Manager just to run the import? The manual gives the impression that there should already be a user listed(I'm assuming the administrator account). Thanks in advance, SD ...

Query regarding A,B,C char. width values returned in 16-bit and 32-bit OS
I am trying to retrieve individual character widths of a string (from a TrueType font) and I tried using GetCharWidth (which weirdly works for TrueType fonts too tho' MSDN says otherwise) and/or GetCharABCWidths to retrieve the A,B and C spacings. Please note that I have selected the TrueType font (hFont) into the Device Context (hDC). I used GetCharABCWidths. Problem is this: GetCharABCWidths returns correct A,B and C widths in Win 98 and incorrect (much lesser A,B and C widths such that the characters OVERLAP) in other 32-bit OS (NT, XP). The effect is seen across all TrueType fonts, th...

"Your path to the data is incorrect" after install
Hi all, I just installed Great Plains on Windows XP, using SQL2000. When I launch the Utilities, I log into my server, and then I select "Launch Great Plains". I am prompted to log into my server again, and then I get the error message: Your path to the data is incorrect. Please enter the location of your Pathnames File. It is prompting me with: Location of Pathnames File: DYNAMICS/dbo/ I see a line in the DEX.INI file: Pathname=DYNAMICS/dbo/ Does anybody have a clue what might be wrong here? Thanx in advance for your time, Walter Have you ran the Utilities to create the...

.NET equivalent to XSLT value-of select
This seems like it should be really easy, but I cannot seem to make it work. I am trying to retrieve the text value of an element named "child2Element" from an XML file in a .NET (v 1.1) with an XPath expression. In an XSLT document I would use <xsl:value-of select="rootElement/child1Element[@childId='110']/child2Element"/> (and this does work fine in a transform). But I cannot seem to find the right method or object in .NET. There seem to be a number of classes that can use XPath, but I can't figure out which one I have to use to get just the te...

Site for SOP Returns
Is it possible to have a different default Site ID for Returns than Standard Orders? Yes. You must make sure to change the site when performing a Return Transaction. -- Richard L. Whaley Author / Consultant / MVP 2006-2008 Documentation for Software Users Get our Free Tips and Tricks Newsletter and check out our books at http://www.AccoladePublications.com "Everton Raymond" wrote: > Is it possible to have a different default Site ID for Returns than Standard > Orders? Sorry, I did not see the word "default" in your post until after I r...

Report for Returns
Anyone know a why to report on frequency of customer returns -- so we can see if any customers are making habit of this? Thanks and regards, Rich hitman, Try running a detailed sales report filtered to only negative sales displayed. Good luck, Matt "hitman" <hitman@discussions.microsoft.com> wrote in message news:0B3EE780-2655-45B4-B3C6-1F5D35FFA2A7@microsoft.com... > Anyone know a why to report on frequency of customer returns -- so we can > see > if any customers are making habit of this? > > Thanks and regards, > > Rich ...

Large file size in Excel
I've got a worksheet that is blank, and the Ctrl-End key combination jumps to cell A1. I originally pasted 5 colums by 138 rows of data into this spreadsheet, then erased the columns completely by using 'Ctrl+-'. I've also selected the entire sheet and used Edit->Clear All. All this to no avail. I have an empty spreadsheet that takes up 900 KB, but zips to 30 KB Any suggestions on how to reduce the file size is welcome. "Jon" <anonymous@discussions.microsoft.com> wrote in message news:69DAD6D5-9876-43C9-B11B-7AC6C19F9CFA@microsoft.com... > I've g...

Copying Data #2
Hi I have a spreadsheet with 2 sheets in it. Sheet 1 has merged cells, single cells and links on it to data contained in other workbooks. at the end of the working day i want to be able to click a macro to copy this information onto 'Sheet2' which has the same formats on it (merged cells, single cells) but no links. In other words i want the macro to copy and paste 'values' only.Thus retaining the information. When i try to record this macro i get a message telling me that 'The cells need to be the same size' which they are. If i just paste this information from sheet ...

UPDATE OF DATA SO
As a data base of UNDER when single is updated I have endorsements of HQ? I need aid ...

Returning the Beginning of the week
Returning the beginning of the week. e.g. if the date == 02/03/04 (Friday 2nd March 2004) the beginning week for that date == 01/03/04 (Thursday 1st March 2004) Hows does one go about achieving this? Regards -- Rizitsu ------------------------------------------------------------------------ Rizitsu's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15840 View this thread: http://www.excelforum.com/showthread.php?threadid=314968 This formula will take a date in cell A1 and give the start of the wee in B1 * assumes that monday is the start of the week, so i...

return
Capability of the system to automatically change the status of the PO to change order, and add line items that have been returned. ---------------- 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 message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.ms...

Deleting a note keeps the value in the NOTEINDX field
I just discovered that if you delete a note from a document (in my case, a cash receipts document), the system will delete the actual note record from the SY03900 table but will leave the original NOTEINDX value on the document record intact. Is this normal behavior? I would think the system should clear out the value in the document record since the note no longer exists. Referential integrity! -- Bud Cool, Accounting System Manager HDA, Inc. Hazelwood, MO GP 9.0, SP2 Yes, this is normal for GP. The record is created the first time the user clicks the note icon and "bookmarks...

No data event function
Hello. I have many reports and am trying to use a function to display a message when there is no data in the report: "Report has no data." The function seems to work in that if there is data, a report runs with the data. However, if there is no data, the report does not run but "Report has no data" does not appear to the user. I'd appreciate suggestions. Public gfReportHasNoData As Boolean Public Function PrintPreviewReport(pstrReport As String, pfPreview As Boolean, pstrWhere As String) As String 'Comments: Print or preview a report and handle errors 'ps...

Reference cell values from other sheets in a function
I am trying to do something that seems like it would be quite simple, and yet I cannot get it to work. I have some data in separate work sheets (one sheet for each month), and a yearly summary sheet. I have a DAVERAGE function in each month. I am trying to take that DAVERAGE on each monthly sheet and make an AVERAGE function in the year summary page referencing each DAVERAGE cell on the monthly sheets. I enter =AVERAGE( into the cell and click over to the other sheets where I need to pull the info from. I click on the DAVERAGE cell that I need to reference in the yearly AVERAGE fu...

Return
When returning an item, can you return the item by selecting the matrix rather than the individual item and changing the quantity to -1? Hi KIm, Normally this won't work as it works in sale where for each matrix type you can put the qunatites. HOWEVER when you select the matrix item it bring the matrix window but allow only one item at a time to be returned and make the -1 qty automatically in return document type. It work but one by one. Regards Akber "Kim" wrote: > When returning an item, can you return the item by selecting the matrix > rather than the individua...

form creating new row in table1, looking for field value in table
Please can someone help as I have been battling all week with this problem, and haven't had a response. I have a form which uses some data from table1 to create an entry in table2 (i know duplication is bad, but as this is a live database, and i am working on a new section, this makes this complicated task easier), which works fine. however, i have a third table that has the unique ID from table1 and table2 in order to show when a table1 row of data is needed in table2 via a checkbox. This third table3 is created when elsewhere. The problem lies with bringing in the unique ID value...

List of values summing to a known value
Is it possible to run a query that would return a list of values that sum to a known quantity. A simplified example would be as follows: Return any items from the list that sum to 10 1 3 4 7 9 The returned values would be 1,9 and 3,7. Thanks. Do you mean something like this -- SELECT [TableA_1].[Auto]+[TableA].[Auto] AS Expr1, TableA.Auto, TableA_1.Auto FROM TableA, TableA AS TableA_1 WHERE ((([TableA_1].[Auto]+[TableA].[Auto])=10)); -- Build a little, test a little. "lmattern" wrote: > Is it possible to run a query that would return a list...

memory stick has lost all data
I (my daughter) have a USB Pen drive with work on it. Today when it plugs into the computer it gives the message that the drive isn't formatted do you want to format it. Well obviously not at the moment but I also can't recover any files. I have tried a number of programs including DDR Pen Drive Recovery and Ontrack Easy Recovery but neither of them find any files at all. If I look on Windows Disk Management it can see the drive (K) and size and also says that it is healthy but again I can't access it. What else can I try to recover the files? Thanks in advance ...