=IF(AND Formula

[Dannytt93]

Hello,

is there anyway to stop excel returning a FALSE message when searching a range? Formula below

=IF(AND('Data Deposit'!A2:A100= "First Name",'Data Deposit'!C2:C100= "Date"),TRUE,FALSE)

If the formula is searching a singular cell, e.g.

=IF(AND('Data Deposit'!A2= "First Name",'Data Deposit'!C2= "Date"),TRUE,FALSE)

The formula works, but a range doesn't. I think due to it returning other false messages, the false ones are overriding the fact that it has found one TRUE in the list.

Any help would be appreciated,

Thanks

Dan

 

[Joe4]

Re: =IF(AND Formula help

You cannot set a whole range equal to a single value like that. That will not compute correctly. That only works with a single cell.

If you want to search a whole range for a value, use something like COUNTIF and see if it returns more than 0 (if it does, then it found the value in the range).

Since AND will return TRUE/FALSE, you do not need the IF at all, i.e.

=AND(COUNTIF('[COLOR=#333333]Data Deposit'!A2:A100,"First Name")>0,[/COLOR]COUNTIF([COLOR=#333333]'Data Deposit'!C2:C100[/COLOR][COLOR=#333333],"Date")>0)[/COLOR]

 

[Dannytt93]

Re: =IF(AND Formula help

you are an absolute legend!! I've spent the past 2 days trying to work out why it isn't working and this is really the first time i have used excel in this capacity so thank you very much!!

 

[Joe4]

Re: =IF(AND Formula help

You are welcome!
Glad I was able to help.

 

[Dannytt93]

Re: =IF(AND Formula help

You are welcome!
Glad I was able to help.

so this is the formula i have ended up with which works perfectly!

=COUNTIF('Data Deposit'!$H$2:$H$100,$B12&"-"&C$11)>0

Is there any way to change the text from saying "true" and "false" to "completed and "not completed"?

Many thanks

Dan

 

[Joe4]

Re: =IF(AND Formula help

Is there any way to change the text from saying "true" and "false" to "completed and "not completed"?

That is where you would wrap it in an IF statement, if you want something other than True or False, i.e.

[COLOR=#333333]=IF(COUNTIF('Data Deposit'!$H$2:$H$100,$B12&"-"&C$11)>0,"completed","not completed")
[/COLOR]