help with formula

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.

Find more posts tagged with

Sheets

Formulas

Comments

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?
Need Help with If, Index, Collect
Please help, I have a few of these to do. I have a nested IF formula. The first part is an INDEX/MATCH and works as a standalone. The next few are INDEX/COLLECT. These are not working as standalone or in the nested version (Unparsable Error). Any ideas? =IF(CONTAINS(“A/V”, [Vendor Type]@row ), INDEX(COLLECT({Date Secured…