# help with formula

edited 12/09/19

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.

Tags:

• ✭✭✭✭✭✭

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

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭

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 <> "[email protected]", [Modified by]@row <> "[email protected]", [Modified by]@row <> "[email protected]"), 1)

.

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

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

• ✭✭✭✭✭✭

Paul's suggestion would work best.﻿

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

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

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

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

• ✭✭✭✭✭✭

Excellent!

Happy to help!

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

Happy to help!

• ✭✭✭✭✭✭

Haha!

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!