# =IF(AND Formula

#### Dannytt93

##### New Member
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

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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.
Code:
``=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]``

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!!

Re: =IF(AND Formula help

You are welcome!
Glad I was able to help.

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

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.
Code:
``````[COLOR=#333333]=IF(COUNTIF('Data Deposit'!\$H\$2:\$H\$100,\$B12&"-"&C\$11)>0,"completed","not completed")
[/COLOR]``````

Replies
2
Views
109
Replies
5
Views
141
Replies
1
Views
129
Replies
5
Views
246
Replies
3
Views
175

1,206,949
Messages
6,075,811
Members
446,158
Latest member
octagonalowl

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back