Check if text exists in rows and columns

[GyroTwister]

Hello all,

I'm doing an overhaul of an Exchange environment of one of my customers. I have listed all needed AD properties, SMTP, smtp and SIP adresses. I want the users to use a new UPN.

In columns L through Q I have put all the current aliasses for these users (some of them have 1 but there are people with 6 aliasses). In column R I have created a new UPN per user. Now I have to find out whether or not my newly created UPN is unique in all the aliases.

I have tried several functions but none of them seems to work as I think they should work.:p

So basically does the value of R2 exists in L2 through Q1094? Their all text fields.

How to go about htis?

[#2]

@GyroTwister 

=COUNT(SEARCH(R2,$L$2:$Q$1094))

Maybe with this formula. Enter formula with ctrl+shift+enter if you don't work with Office365 or 2021.

[#3]

Thanks a lot for your swift reply @OliverScheurich.

I see in the example pdf that all the fields are filled with data however I have several cells which are empty. Does this affect the COUNT? Because the only values I get in return is 1 or 0, while I expected that every value would be there at least once.

Or has it to do with the fact that R2 is a formula to create the new UPN?

With kind regards

[#4 · Accepted solution]

@GyroTwister 

=SUMPRODUCT(--($L$2:$Q$1094=R2))

If there is a newly created UPN in column R the expected result is 0. This means that the newly created UPN doesn't exist in columns L:Q.

If one of the already existing UPN is used as newly created UPN in column R the expected result is 1. This UPN should only appear once in columns L:Q.

The empty cells don't affect the result of the formula as they are not equal to the newly created UPN.

What formula is in cell R2? I can't answer as i don't know which formula is in your spreadsheet in column R. In my example i choose unique new UPN with the exception of "aliasL" in cell R13.