|
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? |
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? |
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? |
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? |
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? |
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? |
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? |
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? |
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? |
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? |
| All times are GMT +1. The time now is 07:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com