help with formula

gery.elrahweise

Hello, 

I'm still quite new with sheets and I wonder someone can help me out with this formula I'm currently struggling with.

What I want to do is to check/compare email addresses.

The following works well when adding formula to a check-box type cell comparing whether the [Modified by] entry is the same like [Email] entry. If it's the same, the check-box is ticked.

=IF([Modified by]@row <> Email@row, 1)

Is it possible at all for me to compare the [Modified by] entry against multiple Email addresses (i.e. ranges) rather than against one email address? Perhaps using INDEX or MATCH? If so, how?

Thank you. 

Andrée Starå

Hi Gery,

Try something like this.

=INDEX(Email:Email; MATCH([Modified by]@row; Email:Email; 0))

The same version but with the below changes for your and others convenience.

=INDEX(Email:Email, MATCH([Modified by]@row, Email:Email, 0))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Paul Newcome

How many different email addresses do you need to compare against? if it is just a few, you could use an AND statement such as this...

 

=IF(AND([Modified by]@row <> "Email1@email.com", [Modified by]@row <> "Email2@email.com", [Modified by]@row <> "Email3@email.com"), 1)

.

If you have an entire table, you can use the CONTAINS function...

 

=IF(NOT(CONTAINS([Modified by]@row, [Email List]:[Email List])), 1)

Andrée Starå

I forgot about the CONTAINS function and I also misread your post.

Paul's suggestion would work best.

Paul Newcome

I almost forgot about the CONTAINS function as well. I was in the process of typing out the FIND/JOIN solution when I had one of those "Hmmmm..." moments. I hadn't used CONTAINS before, so I ran it through some quick testing just to be sure, and it worked beautifully.

 

I've since tried the function out a few different ways, and it still feels really awkward the way it works. I'm sure I'll get used to it though. Haha

Andrée Starå

I didn't get to the Hmmm moment this time. 

gery.elrahweise

Thanks all.. got the formula working to what I want it to do. cheers. 

Andrée Starå

Excellent!

Happy to help!

Paul Newcome

Hahaha. There have been plenty of other times though where the roles were reversed. 

Paul Newcome

Happy to help! 

Andrée Starå

Haha!