| Home |
| Search |
| Today's Posts |
|
#1
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
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
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
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
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
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
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
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
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
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| comparing colum data and exporting | Excel Discussion (Misc queries) | |||
| Keeping a sum colum correct after inserting a colum of data in fro | Excel Discussion (Misc queries) | |||
| Comparing two cells in a colum with 2000 rows | Excel Discussion (Misc queries) | |||
| Look up data in colum a and find match in colum b | Excel Discussion (Misc queries) | |||
| Check data on colum A and find match on colum b | Excel Discussion (Misc queries) | |||
Linear Mode
