Welcome to the forum, but your post is too vague for us to recommend a specific formula, without a lot of assumptions on our part. You refer to "a cell containing an email address" - well, which cell is it, and will it only contain one email address?

You want to look in "another sheet", but what is the sheet name, and which column might the email address be in? Then you want to look in another column for a "specific value". Which column is this, and what is the specific value?

It would save a lot of time if you attached a sample workbook. Instructions on how to do this are given in the yellow banner at the top of the screen.

Hope this helps.

Pete