If then with a list

[Guest]

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

 

[Roger Govier]

Hi

Try
=IF(Sheet2!A1=A1,"YES","NO")

Regards

Roger Govier

 

[pennyrod]

One way: =IF(COUNTIF(Sheet2!A1:Sheet2!A5,A1)>0,"yes", "no")

HTH

Penny

 

[Guest]

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]

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

 

[Elijah]

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.