Solved Can we do IF (And) with cell ranges?

Microsoft Outlook 2013 32/64-bit (mail m...
June 8, 2017 at 14:26:54
Specs: Windows 64
This is my statement:
= IF(or(G2='[Base.xlsx]Additional Customers Log'!$G$2:$G$500;K2='[Base.xlsx]Additional Customers Log'!$K2:$K500);"Duplicate";"PendAssignment")

I've also done it as IF(AND(
but each time it hits the first colon before G2 and stops, giving me the 'we've detected a problem with your formula' pop up. Is there a different way to write ranges that I need to try? This seems like a relatively simple IF statement.


See More: Can we do IF (And) with cell ranges?

Report •

#1
June 8, 2017 at 18:46:24
✔ Best Answer
re: "Can we do IF (And) with cell ranges?"

Not really, but then again, I don't actually know what you are trying to do.

If you are trying to determine if G2 is anywhere within SheetX!G$2:$G$500 AND K2 is
anywhere within SheetX!K$2:$K$500, you can use COUNTIF.

The following formula counts the number of occurrences of G2 and K2 within their
respective ranges. As long as both COUNTIF's return a value that is greater than 0,
the IF is TRUE.

=IF(AND(COUNTIF(SheetX!G2:G500,G2)>0,COUNTIF(SheetX!K2:K500,K2)>0),"Duplicate","PendAssignment")

If that is not what you are trying to do, please provide more details.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
June 9, 2017 at 06:54:10
I haven't tried your countif statement yet, I'll do it soon, but wanted to respond to you first.

The column G holds company names and the K column houses their company numbers. Everyday we get a new list but sometimes the names are repeated, so Duplicate. If the name was not on yesterday's list, it needs to be assigned, so PendAssignment.

IF company name and company number equal company name and number on BASE (wb name), then Duplicate, else PendAssignment

Does this make sense?


Report •

#3
June 9, 2017 at 08:00:41
Thanks for the explanation.

I'll ask that you try my COUNTIF suggestion and then get back to me with the specifics of any issues. I think I understand what you are trying to do, but there's no point in ensuring that I understand your process 100% if the COUNTIF suggestion works.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
June 9, 2017 at 09:18:09
That worked great, DerbyDad. As always, you're a life saver. Thanks!

Report •

Ask Question