Forum Discussion
Check if text exists in rows and columns
=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.
=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.
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=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.
