Home |
Search |
Today's Posts |
#1
|
|||
|
|||
If then with a list
I'm trying to determine if a date in Sheet1 is in a list of dates in Sheet2
(returning a simple, "YES" or "NO"). Example: Sheet1: A1 contains the following date: 09/01/05 B1 should contain the "YES" or "NO" Sheet2: Column A contains a list of dates, such as the following: 01/01/05 02/14/05 09/01/05 12/25/05 12/31/05 What is the best way to return a "YES" or "NO"? I have tried unsuccessfully to do this with an if/then, but I'm thinking there must be a better way. Thanks, Jim |
#2
|
|||
|
|||
If then with a list
Hi
Try =IF(Sheet2!A1=A1,"YES","NO") Regards Roger Govier jim314 wrote: I'm trying to determine if a date in Sheet1 is in a list of dates in Sheet2 (returning a simple, "YES" or "NO"). Example: Sheet1: A1 contains the following date: 09/01/05 B1 should contain the "YES" or "NO" Sheet2: Column A contains a list of dates, such as the following: 01/01/05 02/14/05 09/01/05 12/25/05 12/31/05 What is the best way to return a "YES" or "NO"? I have tried unsuccessfully to do this with an if/then, but I'm thinking there must be a better way. Thanks, Jim |
#3
|
|||
|
|||
If then with a list
One way: =IF(COUNTIF(Sheet2!A1:Sheet2!A5,A1)0,"yes", "no")
HTH Penny "jim314" wrote in message ... I'm trying to determine if a date in Sheet1 is in a list of dates in Sheet2 (returning a simple, "YES" or "NO"). Example: Sheet1: A1 contains the following date: 09/01/05 B1 should contain the "YES" or "NO" Sheet2: Column A contains a list of dates, such as the following: 01/01/05 02/14/05 09/01/05 12/25/05 12/31/05 What is the best way to return a "YES" or "NO"? I have tried unsuccessfully to do this with an if/then, but I'm thinking there must be a better way. Thanks, Jim |
#4
|
|||
|
|||
If then with a list
Actually, I'm trying to see if the value in Sheet1 is _anywhere_ in column A
of Sheet2, not just in A1 of Sheet2. Thanks, though. "Roger Govier" wrote: Hi Try =IF(Sheet2!A1=A1,"YES","NO") Regards Roger Govier jim314 wrote: I'm trying to determine if a date in Sheet1 is in a list of dates in Sheet2 (returning a simple, "YES" or "NO"). Example: Sheet1: A1 contains the following date: 09/01/05 B1 should contain the "YES" or "NO" Sheet2: Column A contains a list of dates, such as the following: 01/01/05 02/14/05 09/01/05 12/25/05 12/31/05 What is the best way to return a "YES" or "NO"? I have tried unsuccessfully to do this with an if/then, but I'm thinking there must be a better way. Thanks, Jim |
#5
|
|||
|
|||
If then with a list
Hi Jim
I'm sorry, I misread your post. I thought you were wanting to do a comparison cell by cell as you copied the formula down the column. To do what you want, =IF(COUNTIF(Sheet2!A:A,"="&A1)0,"YES","NO") Regards Roger Govier jim314 wrote: Actually, I'm trying to see if the value in Sheet1 is _anywhere_ in column A of Sheet2, not just in A1 of Sheet2. Thanks, though. "Roger Govier" wrote: Hi Try =IF(Sheet2!A1=A1,"YES","NO") Regards Roger Govier jim314 wrote: I'm trying to determine if a date in Sheet1 is in a list of dates in Sheet2 (returning a simple, "YES" or "NO"). Example: Sheet1: A1 contains the following date: 09/01/05 B1 should contain the "YES" or "NO" Sheet2: Column A contains a list of dates, such as the following: 01/01/05 02/14/05 09/01/05 12/25/05 12/31/05 What is the best way to return a "YES" or "NO"? I have tried unsuccessfully to do this with an if/then, but I'm thinking there must be a better way. Thanks, Jim |
#6
|
|||
|
|||
If then with a list
It sounds like you might be looking for the VLOOKUP function. For the
example sheets you gave the formula would be: =VLOOKUP(A1,Sheet2!A:A,1,FALSE) The fields in VLOOKUP are as follows (from my example): A1 = What you are looking for Sheet2!A:A = Where to look for it 1 = What to return if it finds the value (This refers to what column it should return the info for if it finds the criteria. The number is relative to the range you search in. For example, in my example I only told it to look in column A (A:A) in sheet 2 (Sheet2!) so it would return the date if it found a match. However if I had told it to look in columns A-C (A:C) and to return 3, it would search column A and if it found the date there it would return whatever was in column C for the row it found the date in (you could make this yes if you wanted). The formula for this example would look like : =VLOOKUP(A1,Sheet2!A:C,3,FALSE)) FALSE = Find only exact matches (true uses partial matches) Note: It will only return the first result, so if the date appears more than 1 time in Sheet 2 it will only return for the first hit. I hope this helps/isnt too jumbled. Let me know if it works. jim314 wrote: Actually, I'm trying to see if the value in Sheet1 is _anywhere_ in column A of Sheet2, not just in A1 of Sheet2. Thanks, though. "Roger Govier" wrote: Hi Try =IF(Sheet2!A1=A1,"YES","NO") Regards Roger Govier jim314 wrote: I'm trying to determine if a date in Sheet1 is in a list of dates in Sheet2 (returning a simple, "YES" or "NO"). Example: Sheet1: A1 contains the following date: 09/01/05 B1 should contain the "YES" or "NO" Sheet2: Column A contains a list of dates, such as the following: 01/01/05 02/14/05 09/01/05 12/25/05 12/31/05 What is the best way to return a "YES" or "NO"? I have tried unsuccessfully to do this with an if/then, but I'm thinking there must be a better way. Thanks, Jim |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find names on list 1 in list 2. list 1 4000 names list 2 400 name | Excel Worksheet Functions | |||
loop trough e-mail address list to send task lists with outlook | Excel Discussion (Misc queries) | |||
LOOP - Adddress List -to email | Excel Discussion (Misc queries) | |||
Refresh a Validation List? | Excel Discussion (Misc queries) | |||
Extracting Values on one list and not another | Excel Discussion (Misc queries) |