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

Similar Articles

[PageSpeed] 18

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:

worksheet shifts to upper left hand corner when I am entering data into a cell
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel When I am entering data into a cell, the worksheet shifts back to the upper left hand corner. When I then hit return, it shifts back to the location of the cell. How can I keep the worksheet in the location where the cell is? ...

Avoid automatic text to column split while pasting data
I am copying data from Access and pasting it in Excel. The column containing "LastName, FirstName" is automatically getting pasted in 2 columns. I want it to get pasted in one column only. Currently ColA ColB John| Doe I need it to be ColA Thank you! John, Doe You probably used Data>Text to Columns>Delimited by "comma" for some other operation. Excel remembers this.........thank you much<g> Run through Data>Text to Columns with some dummy text and change the delimiter to nothing. Gord Dibben MS Excel MVP On Wed, 25 Jun 2008 09:41:00 -0700, Query...

Exporting EXCEL data in text (cvs) format
Is there no way to export EXCEL data to a text format e.g. cvs : "StringField1","StringField2",NumericField3,NumericField4,"StringField5" etc.? You can import from this format, why no export? Any work around? I'm the first person in the universe who wants an importable text version of his EXCEL data? Thanks! Woops! I didn't see the other options in the save as pulldown list. Sorry! Don't bother! "Simon Wigzell" <simonwigzell@shaw.ca> wrote in message news:2FCNb.115599$X%5.16110@pd7tw2no... > Is there no way to export EXCEL da...

how to send individual email to entire contact list
I want to send an individual email to 180 names in one of my contact lists. I do not want to use a distribution llist or have all the email addresses appear in the message. How do I use Outlook 2007 to do that? Use the BCC (Blind Carbon Copy) line instead of To. It will suppress the recipient list so the recipients can't see who else was sent the same mail. -- Kathleen Orland Outlook Tips: http://www.outlook-tips.net/ http://www.howto-outlook.com/ "Keith4321" wrote: > I want to send an individual email to 180 names in one of my contact lists. I > do not want ...

list of valid e-mails
Hi, how can I get list of all active e-mails? Including aliases, public folder and printer addresses if exists and so on? (Exch 2003) Where do the mails for which address does not exist come and how to I forward them to any bin address? Thanks, Jan you can get the list of addresses using ldifde or csvde. Check the following on how to use these tools: http://www.exchangeinbox.com/articles/037/part1_ldifde_csvde.htm http://www.exchangeinbox.com/articles/038/part2_ldifde_csvde.htm You want to catch emails with invalid recipients right? Be aware that this will quickly ...

Great Plains data reference
Good morning everyone, I am looking for a document / reference where I can find a good explaination of what is in the Dynamics tables as well as a field by field explanation as to what it is. What is available in Dynamics is minimal at best and I spend more time exploring the tables to figure out what is in them Any guidance is appreciated! Try searching for Accolade Publications. -- Charles Allen, MVP "ericjcartman" wrote: > Good morning everyone, > > I am looking for a document / reference where I can find a good explaination > of what is in the Dynamics ...

Pivot tables for non-numeric data?
I've got some survey data in which respondents chose among different text descriptors. I want to create a table where the frequency of the different responses are tallied by a couple of different grouping variables. Can I use a pivot table for this? They seem to assume quantitative data. Regardless of the answer to this question, can you provide a hint as to the most straightforward method of creating the crosstab tables? TIA, David Hi, It sounds like you want to do a count, which can easily be done with pivot tables, numeric or non numeric data. What is harder is displaying text re...

When Creating a new User, automatically add to Every Distribution List EXCH2000
Running Microsoft Exchange 2000. When we create a new user in Active Directory, how do we set it up so that user is automatically added to an "everyone" distribution list? Thanks. dvd9@excite.com (dvd9) wrote in message news:<dc01a796.0405120752.782d5ce8@posting.google.com>... > Running Microsoft Exchange 2000. When we create a new user in Active > Directory, how do we set it up so that user is automatically added to > an "everyone" distribution list? Thanks. Bump and posting to exchange.setup Anyone? ...

find formula and return value
Hi i am using the following formula to find text in a cell =IF(ISNUMBER(FIND("Super",B96)),"OK", "Not OK") and return a value. i have adapted the formula for another spreadsheet and simply added more values to check, i.e. =IF(ISNUMBER(FIND("Super""Account""Business:,B96)),"OK", "Not OK") but it doesnt seem to work. where i can see the value in the cell it is not returning ok instead of not ok. is there a limit to the number of values i can search and if so why doesnt it say as an error? what am i missing? Thanks v...

Look up a value in one colum and insert the value found in the adj
I use vlookup when entering data in a row to find a matching item in a two column table and replace the entered data with the suggested item. No problem with this. I then want to pick-up the data in the adjacent column of the table and place that data in a column in the same row where I am entering data. It the data is somewhere to the right of the column in the table that you found a match in that is part of VLOOKUP. http://www.mvps.org/dmcritchie/excel/vlookup.htm If the value is the left, you could rearrange your table; otherwise, use INDEX / MATCH see example for residence...

Change chart type in single data series, Excel 2007
I can't get it to change the chart type on a single data series in the new Excel 2007. The "change chart type" is grayed out. Thank you, What kind of chart is it? If it's a 3D chart, you can't change only one series. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "momof3" <momof3@discussions.microsoft.com> wrote in message news:F78063A1-DB29-4081-B909-BDA99F2DEBFD@microsoft.com... >I can't get it to change the chart type on a single data series ...

Changing Values by Grouping and Dragging
Using OL 2003 Is there any way to change the subject line of a bunch of tasks or appointments at the same time? I used to be able to do this by grouping the items by subject and then dragging the highlited items to a different group but it won't work now with OL2003 Same thing for changing the Contacts associated with a Task or Appointment. Thanks ...

Unable to convert money 2001 data to 2005
Hi Ho, How to Convert 2001 data to 2005 Thanking Yoiu/ Presuming both the versions of 01 and 05 were produced in the same country, it should just work. What error do you get? -- "George J Meier" <jost@meier.screaming.net> wrote in message news:%23qoGmWOmFHA.3568@tk2msftngp13.phx.gbl... > Hi Ho, > > How to Convert 2001 data to 2005 > > Thanking Yoiu/ > Thanking you Richard, I am doing this for the person who had the trouble. He said the programme asked him which country he was in, and he said England. The programme then asked him if he wanted...

How can I make individual x values?
How can I make a graph which each curve has individual x-axis values? Like the example below http://www.excelforum.com/attachment.php?attachmentid=5136&stc=1&d=1154477626 ctgrcrvs.gif +-------------------------------------------------------------------+ |Filename: ctgrcrvs.gif | |Download: http://www.excelforum.com/attachment.php?postid=5136 | +-------------------------------------------------------------------+ -- gpktm ------------------------------------------------------------------------ gpktm's Profile: http://www.excelfo...

Passing a cell value into a text box on a graph
Please help. I am trying to pass a value from a cell onto a graph. The graph is plotting culumative variance against a plan but the gap in the early days is very small. So I need to publish the gap between plan and actual on the graph. I want to automate the update of the gap on opening the spreadsheet or pressing a button. The calculation of the gap is no problem for me, its the putting the answer onto the graph is the issue. I thought I could do it via a text box on the graph. Thanks in advance John Please help. I am trying to pass a value from a cell onto a graph. The graph is plot...

How can I vary graph color by range value
I would like to create a chart where the color of the bar varies depending on the point value. For example the range 0-1.99- red, 2.00 through 2.99 - yellow, and 2.99 through 5.00 - green. Any ideas? The easiest way to do this is to have a data series for the RED range, another for YELLOW and another for GREEN. You can then create a "stacked" column or bar chart. I'm assuming here that if you want a point in GREEN, you won't also want it in RED. "pcover" wrote: > I would like to create a chart where the color of the bar varies depending on > t...

How can i make values appear at the top of bars in a bar graph?
chart options>data labels>value -- Don Guillett SalesAid Software donaldb@281.com "Dylan" <Dylan@discussions.microsoft.com> wrote in message news:9AF2AB4B-03DE-4A30-9435-83C9C517D92F@microsoft.com... > ...

Is there a way to parse multiple values in a cell and apply calculations on those values ?
Is there a way to parse multiple values in a cell and apply a calculation on those values ? The Inventory column has a formula to determine the total inventory of the store where each unique item has its quantity appended in parentheses to the item=92s name. Each unique item is separated by a comma. Each item=92s price will change over time and VLOOKUP can be used to determine the current price of that item on another worksheet. What I=92m trying to do is: 1) Parse out the multiple values in the Inventory column on the INVENTORY worksheet 2) VLOOKUP each item=92s current price on ...

Sum corresponding row values for Saturday only dates within a date range
I am trying to find all Saturdays within a range(on row 14), and if a Saturday is detected, then I would like to add the values in the corresponding row for the Saturday only dates(available in row 16). After going through the date range, and the total sum is zero, then I would like to return false. I also am trying to avoid doing this via a macro as it should be automatically re-calculated if a cell changes. Currently I have the cell formatted as follows that will provide a true or false, if any Saturday is detected with values in the corresponding row. I created a IsSaturday function, tha...

trying to link data between workbooks and worksheets
Below is what the Excel XP Help says about linking, and it makes no sense to me, nor does it work. How do I really link data between separate workbooks? What I want to do is see the same information in the Destination sheet as in the Source sheet, and keep the info current. Merely copying it won't keep it current. And how does Microsoft get by with writing such sloppy work? Create a link between cells in different workbooks 1.. Open both the workbook that will contain the link (called the destination workbook), and the workbook that contains the data you want to link to (called the s...

sorting out duplicate data of slight variation
hi. in a school district volunteer effort, i have a need to sort out rows of data which are duplicates of other rows of data yet some fields contain different data. picture this: columns: first name, last name, address rows contain data like below: susan, smith, 1440 wallaby way joe, smith, 1440 wallaby way bart, smith, 200 kings court i need to keep susan or joe but not both. HELP! Hi Carl, You can use a filter, but I expect your initial goal is to identify them and chose one of them at the primary one to keep. Chip Pearson has a rather complete collection of dealing with duplicates. ...

field default value
I have setup default picklist values on Account, the default field values only works when an account is newly created, not converted from a lead. For instance on the shipping field if I create a new account (not from lead), then it defaults to UPS, if I convert to Account from lead then the field does not default. Since this is not a mapping do I have to use a onload event to fill in fields? Unfortuately, defaults only work when the records are created from the form. If you really want those values, one option would be to add similar field to the Lead (with defaults) and then supply ma...

"John Smith" in source data appears as "t k" in PivotTable
PivotTables are wonderful, but this one has me stumped: I have a table which includes the field "Task Resource", where individual names are listed. There is a PivotTable generated from this list, which groups task by owner, phase, etc. It works great, except that one engineer -- let's call him "John Smith" -- appears as "t k" in the Task Resource column of the PivotTable. When I go back to the source data, his name is John Smith. It is a text string, not a formula. When I use the PivotTable to drill down, his name in Task Resource column is John Smith. I&...

Grouping Data to New Work Book
Hi I would be grateful if someone could help me with this problem please... I cuurently have a workbook with the sheets designed as a diary (each sheet being one date. On each row of a partiuclar sheet i enter different jobs under various headings in the columns One of the columns is for a persons payroll number and is unique to that person. What i am trying to do is to group all the entries for one person on to another worksheet so at the end of the month it will show all the jobs that person has done for the month. I have tried (very unsuccesfully!!) to do this myself so would really a...

Complicated data entry ?
I would like to have a spreadsheet on which I can enter services automatically. Problem is each differrent service has different numbers of elements which make it up. For example: FRAME PORT LINE PVCa PVCb ROUTER ATM PORT LINE SCR SMTP SUBS TRAFFIC AV SCAN And I want, on another sheet, to choose (from a drop down), one of the services (in this case, FRAME or ATM or SMTP) and get the service elements automatically added so the result looks like FRAME PORT FRAME LINE FRAME PVCa FRAME PVCb FRAME ROUTER SMTP SUBS SMTP TRAFFIC SMTP AV SCAN I'...