EXCEL formulas - return true if the same value in one of three other columns

Question

I have an Excel database with multiple sheets. There's this one sheet with general data (like emails etc.), then I have another sheet that contains email addresses subscribed to a newsletter (the emails are simply row after row in the first column). In the first "general" sheet I have three separate columns with email addresses in each row. I need to check whether there's an email in those three columns that's in the newsletter sheet - whether a person is subscribed to the newsletter or not - if so, I want to put the email into a column next to it, or just simply writing subscribed into it.

I had already this formula: =IFERROR(VLOOKUP($L2, Newsletter!A:A, 1, FALSE),"") , but this works only if the emails are stored just in one column.

Here is how the database SHOULD look like - three columns with emails and another newsletter-check-column (now the newsletter column isn't working ofc):

My newsletter sheet looks very simple:

Is there are formula for this or do I have to make a VBA Macro for this?

Answer 1

try,

=IFERROR(VLOOKUP($L2, Newsletter!A:A, 1, FALSE), IFERROR(VLOOKUP($m2, Newsletter!A:A, 1, FALSE), IFERROR(VLOOKUP($n2, Newsletter!A:A, 1, FALSE), "not subscribed")))

Answer 2

I solved it already, thanks for everyone's help.

The formula: =IF(OR(NOT(ISNA(VLOOKUP($L4, Newsletter!A:A, 1, FALSE))),NOT(ISNA(VLOOKUP($M4, Newsletter!A:A, 1, FALSE))),NOT(ISNA(VLOOKUP($N4, Newsletter!A:A, 1, FALSE)))),"YES","NO")

Answer 3

There are a number of ways you could do this. I like array functions, so I would do it like this:

In column O, you could paste something along the lines of

{=SUM(IF(ISBLANK(Newsletter!A:A),0,IF($L2=Newsletter!A:A,1,IF($M2=Newsletter!A:A,1,IF($N2=Newsletter!A:A,1,0)))))}

Note: This is an array formula, which means you need to paste it into a cell, then hit ctrl+shift+enter then copy that cell down your list.

The first if statement checks to make sure the blank cells in the list of emails from your 'Newsletter' tab don't match the blank cells in your 'Database' tab. The next three if statements sequentially check columns L, M, and N, to see if those emails exist in the big list.

Depending on the size of your lists, it might be quicker to limit the length of A, to something more like Newsletter!$A$1:$A$100000 or however long you need it so you are not checking all of the million+ rows that exist in Newsletter!A:A four times for every row in the database tab.

If it is a one time thing, its not a big deal, but if you are constantly updating the list, it might be quicker to run a vba script.

Answer 4

A link to a sample worksheet: Click Here

For a much simpler solution than the one you provided, try this:

1. Create a dynamically sized named range: Click the button New..., give it a name (eg: Emails), and for the formula use: =OFFSET(Sheet1!$F$2,0,0,COUNTA(Sheet1!$F:$F)-1,1) (please note: the formulas I'm using correspond with the attached screenshot)
2. For the formula in the "Newsletter" column use: =IF(OR(Emails=A2, Emails=B2,Emails=C2),TRUE,FALSE). Enter this formula as an array formula (press CTRL+SHIFT+ENTER and it will put { } around the formula and you know you've done it correctly).
3. Drag this formula down for the height of the column.

The way this formula works is by first limiting the number of cells that it has to constantly reference to make the calculation (Step 1 above).

Next, using a simple OR statement, we match each column against the dynamically sized list. If any of the cells are a match, it will return TRUE.