|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 18th, 2009, 06:59 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
COmparing a cell with a colum of data
Hi iam having trouble wiht my formula
=IF(A2='[Area 43-52 New Floc Upload.xls]Area 43-52 New Floc Upload'!$A:$A,"New","Old") or even =IF(K12=L1:L15,"YAY","NA") It will only check if the data is on the same row. I want excel to compare one particular cell with all the cells in a whole entrie column for a match and then tell me yes or no. How do i do this? 
|
|
#2
May 18th, 2009, 07:14 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
COmparing a cell with a colum of data
You need to enter a formula for each line to receive a Yay/Nay response for
each. The way you've entered it is indicative of a formulaic array, however, you would need to encapsulate it further for the data to make sense. -- Regards, Eddie http://www.HelpExcel.com "Dingerz" wrote: Hi iam having trouble wiht my formula =IF(A2='[Area 43-52 New Floc Upload.xls]Area 43-52 New Floc Upload'!$A:$A,"New","Old") or even =IF(K12=L1:L15,"YAY","NA") It will only check if the data is on the same row. I want excel to compare one particular cell with all the cells in a whole entrie column for a match and then tell me yes or no. How do i do this?
|
|
#3
May 18th, 2009, 07:18 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
COmparing a cell with a colum of data
Try this
=IF(ISERROR(MATCH(K12,L1:L15,0)),"NA","YAY") If this post helps click Yes --------------- Jacob Skaria "Dingerz" wrote: Hi iam having trouble wiht my formula =IF(A2='[Area 43-52 New Floc Upload.xls]Area 43-52 New Floc Upload'!$A:$A,"New","Old") or even =IF(K12=L1:L15,"YAY","NA") It will only check if the data is on the same row. I want excel to compare one particular cell with all the cells in a whole entrie column for a match and then tell me yes or no. How do i do this?
|
|
#4
May 18th, 2009, 07:19 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
COmparing a cell with a colum of data
Can you please show me what formula i must use.
=IF(K12=L1:L15,"YAY","NA") It will only check if the data is on the same row. I want excel to compare one particular cell with all the cells in a whole entrie column for a match and then tell me yes or no. "HelpExcel.com" wrote: You need to enter a formula for each line to receive a Yay/Nay response for each. The way you've entered it is indicative of a formulaic array, however, you would need to encapsulate it further for the data to make sense. -- Regards, Eddie http://www.HelpExcel.com "Dingerz" wrote: Hi iam having trouble wiht my formula =IF(A2='[Area 43-52 New Floc Upload.xls]Area 43-52 New Floc Upload'!$A:$A,"New","Old") or even =IF(K12=L1:L15,"YAY","NA") It will only check if the data is on the same row. I want excel to compare one particular cell with all the cells in a whole entrie column for a match and then tell me yes or no. How do i do this?
|
|
#5
May 18th, 2009, 07:28 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
COmparing a cell with a colum of data
Thanks Jacob that was helpful. Can you help me further....
As well as saying Yay or Nay how do i make the cell tell me which cells a match was found in. Eg: Yay, L1,L2,L6 Also when i drag down a series of formulas using the crosshair that appears in the botoom right hand corner of the cell it changes the formula. For example, =IF(ISERROR(MATCH(I7,A1:A15,0)),"NA","YAY") =IF(ISERROR(MATCH(I8,A2:A16,0)),"NA","YAY") I want only one column to increment by this i mean: =IF(ISERROR(MATCH(I7,A1:A15,0)),"NA","YAY") =IF(ISERROR(MATCH(I8,A1:A15,0)),"NA","YAY") =IF(ISERROR(MATCH(I9,A1:A15,0)),"NA","YAY") How do i do this without having to type the formula in over and over again. Thanks for your hlp "Jacob Skaria" wrote: Try this =IF(ISERROR(MATCH(K12,L1:L15,0)),"NA","YAY") If this post helps click Yes --------------- Jacob Skaria "Dingerz" wrote: Hi iam having trouble wiht my formula =IF(A2='[Area 43-52 New Floc Upload.xls]Area 43-52 New Floc Upload'!$A:$A,"New","Old") or even =IF(K12=L1:L15,"YAY","NA") It will only check if the data is on the same row. I want excel to compare one particular cell with all the cells in a whole entrie column for a match and then tell me yes or no. How do i do this?
|
|
#6
May 18th, 2009, 07:32 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
COmparing a cell with a colum of data
For the second query use absolute reference like below
=IF(ISERROR(MATCH(I7,$A$1:$A$15,0)),"NA","YAY") -- If this post helps click Yes --------------- Jacob Skaria "Dingerz" wrote: Thanks Jacob that was helpful. Can you help me further.... As well as saying Yay or Nay how do i make the cell tell me which cells a match was found in. Eg: Yay, L1,L2,L6 Also when i drag down a series of formulas using the crosshair that appears in the botoom right hand corner of the cell it changes the formula. For example, =IF(ISERROR(MATCH(I7,A1:A15,0)),"NA","YAY") =IF(ISERROR(MATCH(I8,A2:A16,0)),"NA","YAY") I want only one column to increment by this i mean: =IF(ISERROR(MATCH(I7,A1:A15,0)),"NA","YAY") =IF(ISERROR(MATCH(I8,A1:A15,0)),"NA","YAY") =IF(ISERROR(MATCH(I9,A1:A15,0)),"NA","YAY") How do i do this without having to type the formula in over and over again. Thanks for your hlp "Jacob Skaria" wrote: Try this =IF(ISERROR(MATCH(K12,L1:L15,0)),"NA","YAY") If this post helps click Yes --------------- Jacob Skaria "Dingerz" wrote: Hi iam having trouble wiht my formula =IF(A2='[Area 43-52 New Floc Upload.xls]Area 43-52 New Floc Upload'!$A:$A,"New","Old") or even =IF(K12=L1:L15,"YAY","NA") It will only check if the data is on the same row. I want excel to compare one particular cell with all the cells in a whole entrie column for a match and then tell me yes or no. How do i do this?
|
|
#7
May 18th, 2009, 07:34 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
COmparing a cell with a colum of data
Thanks Jacob any ideas on the frist part?
"Jacob Skaria" wrote: For the second query use absolute reference like below =IF(ISERROR(MATCH(I7,$A$1:$A$15,0)),"NA","YAY") -- If this post helps click Yes --------------- Jacob Skaria "Dingerz" wrote: Thanks Jacob that was helpful. Can you help me further.... As well as saying Yay or Nay how do i make the cell tell me which cells a match was found in. Eg: Yay, L1,L2,L6 Also when i drag down a series of formulas using the crosshair that appears in the botoom right hand corner of the cell it changes the formula. For example, =IF(ISERROR(MATCH(I7,A1:A15,0)),"NA","YAY") =IF(ISERROR(MATCH(I8,A2:A16,0)),"NA","YAY") I want only one column to increment by this i mean: =IF(ISERROR(MATCH(I7,A1:A15,0)),"NA","YAY") =IF(ISERROR(MATCH(I8,A1:A15,0)),"NA","YAY") =IF(ISERROR(MATCH(I9,A1:A15,0)),"NA","YAY") How do i do this without having to type the formula in over and over again. Thanks for your hlp "Jacob Skaria" wrote: Try this =IF(ISERROR(MATCH(K12,L1:L15,0)),"NA","YAY") If this post helps click Yes --------------- Jacob Skaria "Dingerz" wrote: Hi iam having trouble wiht my formula =IF(A2='[Area 43-52 New Floc Upload.xls]Area 43-52 New Floc Upload'!$A:$A,"New","Old") or even =IF(K12=L1:L15,"YAY","NA") It will only check if the data is on the same row. I want excel to compare one particular cell with all the cells in a whole entrie column for a match and then tell me yes or no. How do i do this?
|
|
#8
May 18th, 2009, 07:43 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
COmparing a cell with a colum of data
Using a formula you can get the number of instances; but to return the cell
references as a comma separated text you will have to use a UserDefined Function. If this post helps click Yes --------------- Jacob Skaria "Dingerz" wrote: Thanks Jacob any ideas on the frist part? "Jacob Skaria" wrote: For the second query use absolute reference like below =IF(ISERROR(MATCH(I7,$A$1:$A$15,0)),"NA","YAY") -- If this post helps click Yes --------------- Jacob Skaria "Dingerz" wrote: Thanks Jacob that was helpful. Can you help me further.... As well as saying Yay or Nay how do i make the cell tell me which cells a match was found in. Eg: Yay, L1,L2,L6 Also when i drag down a series of formulas using the crosshair that appears in the botoom right hand corner of the cell it changes the formula. For example, =IF(ISERROR(MATCH(I7,A1:A15,0)),"NA","YAY") =IF(ISERROR(MATCH(I8,A2:A16,0)),"NA","YAY") I want only one column to increment by this i mean: =IF(ISERROR(MATCH(I7,A1:A15,0)),"NA","YAY") =IF(ISERROR(MATCH(I8,A1:A15,0)),"NA","YAY") =IF(ISERROR(MATCH(I9,A1:A15,0)),"NA","YAY") How do i do this without having to type the formula in over and over again. Thanks for your hlp "Jacob Skaria" wrote: Try this =IF(ISERROR(MATCH(K12,L1:L15,0)),"NA","YAY") If this post helps click Yes --------------- Jacob Skaria "Dingerz" wrote: Hi iam having trouble wiht my formula =IF(A2='[Area 43-52 New Floc Upload.xls]Area 43-52 New Floc Upload'!$A:$A,"New","Old") or even =IF(K12=L1:L15,"YAY","NA") It will only check if the data is on the same row. I want excel to compare one particular cell with all the cells in a whole entrie column for a match and then tell me yes or no. How do i do this?
|
|
#9
May 18th, 2009, 07:50 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
COmparing a cell with a colum of data
Thanks jacob,
If i can tdo that is it possible to make it tell me the contents of the cell next to it. For example: =IF(ISERROR(MATCH(I9,A1:A15,0)),"NA",******CELL CONTENTS OF B.XXX.***) Where B.XXX. is row B and the column where a match was found "Jacob Skaria" wrote: Using a formula you can get the number of instances; but to return the cell references as a comma separated text you will have to use a UserDefined Function. If this post helps click Yes --------------- Jacob Skaria "Dingerz" wrote: Thanks Jacob any ideas on the frist part? "Jacob Skaria" wrote: For the second query use absolute reference like below =IF(ISERROR(MATCH(I7,$A$1:$A$15,0)),"NA","YAY") -- If this post helps click Yes --------------- Jacob Skaria "Dingerz" wrote: Thanks Jacob that was helpful. Can you help me further.... As well as saying Yay or Nay how do i make the cell tell me which cells a match was found in. Eg: Yay, L1,L2,L6 Also when i drag down a series of formulas using the crosshair that appears in the botoom right hand corner of the cell it changes the formula. For example, =IF(ISERROR(MATCH(I7,A1:A15,0)),"NA","YAY") =IF(ISERROR(MATCH(I8,A2:A16,0)),"NA","YAY") I want only one column to increment by this i mean: =IF(ISERROR(MATCH(I7,A1:A15,0)),"NA","YAY") =IF(ISERROR(MATCH(I8,A1:A15,0)),"NA","YAY") =IF(ISERROR(MATCH(I9,A1:A15,0)),"NA","YAY") How do i do this without having to type the formula in over and over again. Thanks for your hlp "Jacob Skaria" wrote: Try this =IF(ISERROR(MATCH(K12,L1:L15,0)),"NA","YAY") If this post helps click Yes --------------- Jacob Skaria "Dingerz" wrote: Hi iam having trouble wiht my formula =IF(A2='[Area 43-52 New Floc Upload.xls]Area 43-52 New Floc Upload'!$A:$A,"New","Old") or even =IF(K12=L1:L15,"YAY","NA") It will only check if the data is on the same row. I want excel to compare one particular cell with all the cells in a whole entrie column for a match and then tell me yes or no. How do i do this?
|
|
#10
May 18th, 2009, 08:54 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
COmparing a cell with a colum of data
To returrn value from B for a match in A try this
=IF(ISERROR(MATCH(I9,A1:A15,0)),"NA",INDEX(B1:B15, MATCH(I9,A1:A15,0))) Alternatively you may use the VLOOKUP function If this post helps click Yes --------------- Jacob Skaria "Dingerz" wrote: Thanks jacob, If i can tdo that is it possible to make it tell me the contents of the cell next to it. For example: =IF(ISERROR(MATCH(I9,A1:A15,0)),"NA",******CELL CONTENTS OF B.XXX.***) Where B.XXX. is row B and the column where a match was found "Jacob Skaria" wrote: Using a formula you can get the number of instances; but to return the cell references as a comma separated text you will have to use a UserDefined Function. If this post helps click Yes --------------- Jacob Skaria "Dingerz" wrote: Thanks Jacob any ideas on the frist part? "Jacob Skaria" wrote: For the second query use absolute reference like below =IF(ISERROR(MATCH(I7,$A$1:$A$15,0)),"NA","YAY") -- If this post helps click Yes --------------- Jacob Skaria "Dingerz" wrote: Thanks Jacob that was helpful. Can you help me further.... As well as saying Yay or Nay how do i make the cell tell me which cells a match was found in. Eg: Yay, L1,L2,L6 Also when i drag down a series of formulas using the crosshair that appears in the botoom right hand corner of the cell it changes the formula. For example, =IF(ISERROR(MATCH(I7,A1:A15,0)),"NA","YAY") =IF(ISERROR(MATCH(I8,A2:A16,0)),"NA","YAY") I want only one column to increment by this i mean: =IF(ISERROR(MATCH(I7,A1:A15,0)),"NA","YAY") =IF(ISERROR(MATCH(I8,A1:A15,0)),"NA","YAY") =IF(ISERROR(MATCH(I9,A1:A15,0)),"NA","YAY") How do i do this without having to type the formula in over and over again. Thanks for your hlp "Jacob Skaria" wrote: Try this =IF(ISERROR(MATCH(K12,L1:L15,0)),"NA","YAY") If this post helps click Yes --------------- Jacob Skaria "Dingerz" wrote: Hi iam having trouble wiht my formula =IF(A2='[Area 43-52 New Floc Upload.xls]Area 43-52 New Floc Upload'!$A:$A,"New","Old") or even =IF(K12=L1:L15,"YAY","NA") It will only check if the data is on the same row. I want excel to compare one particular cell with all the cells in a whole entrie column for a match and then tell me yes or no. How do i do this?
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
