If then statement help - excel 1

[ITALIAORIANA]

Hello,

Is it possible for excel to search a range on Workbook 1 and compare it to a cell in Workbook 2, then populate a cell on Workbook 2 with a yes or no?

I am using this formula and it is not working. I keep getting the #value erorr.

=IF(B4='[ASCO Detail 110209.xls]Sheet1'!$A$6:$A$539,"yes","no")

I am looking at B4 cell on workbook 2 and I want it to search cells A6 through A539 in workbook 1. If the value in B4 exists anywhere in the range in workbook 1, then I want it to populate my cell in workbook 2 with "yes". If it doesn't then I want to populate my cell in workbook 2 with "no"

Any help would be greatly appreciated.
Thanks
Deana

 

[randy700]

The LOOKUP function should do it.

=IF(LOOKUP(B4,A6:A539)=B4,"YES","NO")

Randy

 

[ITALIAORIANA]

Wooo Hooo Thanks so much Randy700!!! You Rock - this worked exactly like I needed it to. I wasn't even trying the lookup function. I never needed to do a range before.

I just added the workbook name before the range and it's perfect.


Thanks again,
Deana

 

[AnotherHiggins]

Just to offer another method to skin this cat....

CountIf takes less overhead than a VLookup (although that probably won't be a concern unless your workbook is pretty large).
[tab]=If(Countif(A6:A539, B4)>0, "Yes", "No")


Also, if you don't mind seeing TRUE/FALSE instead of YES/NO, then you can omit the If function altogether.

Try:
[tab][COLOR=blue white]=Countif(A6:A539, B4)>0[/color]

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.