IF formula resulting in FALSE for some users, but not others

[jacarroll09]

I'm using the following formula that refers to a sheet named "NetworkList" within the same workbook to tell the user whether a geographic location in column E is in or out of a specific network. The NetworkList sheet lists the in and out of network locations in column A and B. When some users run the formula, it works properly. When others run the formula, every cell says FALSE. All users in question are using Office 365. Does anybody know what could be going wrong here, and what can I do to get this running correctly for all users?

The formula is below:

=IF(SUM(N(ISNUMBER(SEARCH(NetworkList!$A$1:$A$299,E2))))>0,"in-network",IF(SUM(N(ISNUMBER(SEARCH(NetworkList!$B$1:$B$55,E2))))>0,"out-of-network"))

 

[jtakw]

Hi,

1st, you're missing a "value if false" at the end of your IF statement:

=IF(SUM(N(ISNUMBER(SEARCH(NetworkList!$A$1:$A$299,E2))))>0,"in-network",IF(SUM(N(ISNUMBER(SEARCH(NetworkList!$B$1:$B$55,E2))))>0,"out-of-network",""))

2nd, I don't think that formula is doing what you described, are you looking for E2 within either Column A or Column B for result ?

 

[Fluff]

Hi & welcome to MrExcel.
If the value in E2 is an exact match for either col A or B, you can use

=IF(COUNTIFS(Networklist!$A$1:$A$299,E2),"in-network",IF(COUNTIFS(Networklist!$B$1:$B$55,E2),"out-of-network",""))

if it's a partial match then you could use

=IF(COUNTIFS(Networklist!$A$1:$A$299,"*"&E2&"*"),"in-network",IF(COUNTIFS(Networklist!$B$1:$B$55,"*"&E2&"*"),"out-of-network",""))

 

[jtakw]

I wrote those Exact formulas, except I used COUNTIF, haven't posted it, was waiting for OP's answer.

 

[jacarroll09]

Hi,

1st, you're missing a "value if false" at the end of your IF statement:

=IF(SUM(N(ISNUMBER(SEARCH(NetworkList!$A$1:$A$299,E2))))>0,"in-network",IF(SUM(N(ISNUMBER(SEARCH(NetworkList!$B$1:$B$55,E2))))>0,"out-of-network",""))

2nd, I don't think that formula is doing what you described, are you looking for E2 within either Column A or Column B for result ?

Thanks for responding! I'm looking in E2 on Sheet1 for a value between A1:A299 on a second sheet labeled NetworkList. This works for me and a few others on my team, but will not work for one other user. It just produces false no matter what.

 

[jtakw]

Thanks for responding! I'm looking in E2 on Sheet1 for a value between A1:A299 on a second sheet labeled NetworkList. This works for me and a few others on my team, but will not work for one other user. It just produces false no matter what.

The missing "value if false", as I mentioned before, see Post # 2, I added "" at the very end of your IF formula, Bold in Red would fix the FALSE result.

However, also as I mentioned, I don't think that formula is the best approach, if it actually works at all.

Here's what I came up with, pretty much the same as what Fluff posted above:

Use this for Exact match of E2 within the range of A1:A299 or B1:B55

=IF(COUNTIF(NetworkList!$A$1:$A$299,E2),"in-network",IF(COUNTIF(NetworkList!$B$1:$B$55,E2),"out-of-network",""))

Use this for Partial match of E2 within said ranges

=IF(COUNTIF(NetworkList!$A$1:$A$299,"*"&E2&"*"),"in-network",IF(COUNTIF(NetworkList!$B$1:$B$55,"*"&E2&"*"),"out-of-network",""))

Test these formulas out in a Copy of your workbook and see if it works the way you want.

If Not, can you give a sample of what's in E2, and what might be in A1:A299 and/or B1:B55 ?

 

[jacarroll09]

The missing "value if false", as I mentioned before, see Post # 2, I added "" at the very end of your IF formula, Bold in Red would fix the FALSE result.

However, also as I mentioned, I don't think that formula is the best approach, if it actually works at all.

Here's what I came up with, pretty much the same as what Fluff posted above:

Use this for Exact match of E2 within the range of A1:A299 or B1:B55

=IF(COUNTIF(NetworkList!$A$1:$A$299,E2),"in-network",IF(COUNTIF(NetworkList!$B$1:$B$55,E2),"out-of-network",""))

Use this for Partial match of E2 within said ranges

=IF(COUNTIF(NetworkList!$A$1:$A$299,"*"&E2&"*"),"in-network",IF(COUNTIF(NetworkList!$B$1:$B$55,"*"&E2&"*"),"out-of-network",""))

Test these formulas out in a Copy of your workbook and see if it works the way you want.

If Not, can you give a sample of what's in E2, and what might be in A1:A299 and/or B1:B55

Thanks again for your help with this. I tried the formulas you provided, but they resulted in #VALUE! The data in E is a list of geographic locations, with an example as follows:

1. Department of Computer Science, Georgia State University, Atlanta, GA 30302, USA
2. College of Computer Science, Sichuan University, Chengdu 610065, China
3. Arab Academy for Science, Technology and Maritime Transport, Cairo 11799, Egypt

An example of the data in A and B are in the table below:

A	B
Atlanta	Chengdu
Boston	Johannesburg
New York	Melbourne

If the date above were to appear in E, the results should be as below:

1. in network
2. out of network
3. FALSE

The formula as I posted it originally works for me. It just doesn't work for one other user. I can't figure out a reason why the same formula will work for one user and not the other!

Thanks again for taking the time to help me with this!

 

[jtakw]

Ok, I understand what you're trying to do now.

So your original formula Should work for your purpose, but Not working for 1 or more of your users.
For Non-365 Excel, that formula requires to be array entered ( as I have 2016 ), but you say All your users are on 365, so that's puzzling.
You can have the non-working users Array enter the formula and see if that fixes it.

OR, perhaps, try my formula below, which is Not array entered, and see if it would work for everyone.

=IF(ISNUMBER(LOOKUP(9^9,SEARCH(NetworkList!$A$1:$A$299,E2))),"in-network",IF(ISNUMBER(LOOKUP(9^9,SEARCH(NetworkList!$B$1:$B$55,E2))),"out-of-network"))

 

[jacarroll09]

Thanks once again! The last formula works for me, so I'll try that on Monday with the other user. I'll also try having them enter it as an Array.

I'm really not well-versed in this stuff, as you might be able to tell. Do you mind explaining the difference between your formula and my original? I'd love to understand how it works for future reference. Mine original formula is posted below again:

=IF(SUM(N(ISNUMBER(SEARCH(NetworkList!$A$1:$A$299,E2))))>0,"in-network",IF(SUM(N(ISNUMBER(SEARCH(NetworkList!$B$1:$B$55,E2))))>0,"out-of-network"))

 

[jtakw]

Good, just make CERTAIN, do Not array enter my formula in Post # 8

Post back and let us know if it works.