Using IF statement in excel

[rgaetos]

How do I write an if statement to compare a cell to a range of cell? What I want to do is compare a name to a list of names on a sparate spreadsheet and mark it if it finds match on that list.

 

[Lilliabeth]

Maybe something like this...
Assuming the name you are looking for is in G1 and the list is Sheet2 - A1:A12


=IF(COUNTIF(Sheet2!$A$1:$A$12,G1)>0,"Present","")

Lilliabeth
-Why use a big word when a diminutive one will do?-

 

[rgaetos]

That worked so that I can mark it, but what if I want the output to be a date that is related to the cell that matches my comparison.

So, say my list my list is Sheet2-A1:A12 and the dates that correspond with each name is Sheet2-B1:B12.

If there is a match, I want the date listed in Sheet2-B1:B12 to be the output of the cell.

 

[Lilliabeth]

=IF(ISNA(VLOOKUP(G1,Sheet2!$A$1:$B$12,2,FALSE)),"",VLOOKUP(G1,Sheet2!$A$1:$B$12,2,FALSE))

Lilliabeth
-Why use a big word when a diminutive one will do?-

 

[rgaetos]

I'm using that previous function, but rather than pulling a corresponding date field I am trying to pull a text field, but if there is no text in the field the output is a 0. How to I get it to output "blank" if there is no text to pull from the other sheet?

 

[rgaetos]

On that same note, if there is no date given it gives January 0, 1900 as the date. How do I get the output to be blank rather than that date?

 

[SkipVought]

Hi,


Regarding dates...

Why do Dates and Times seem to be so much trouble? faq68-5827
[tt]
=IF(ISNA(VLOOKUP(G1,Sheet2!$A$1:$B$12,2,FALSE)),"",if(VLOOKUP(G1,Sheet2!$A$1:$B$12,2,FALSE)=0,"",VLOOKUP(G1,Sheet2!$A$1:$B$12,2,FALSE)))
[/tt]



Skip,
[sub]
[red][/red]
[/sub]