CODEWITHSUNDEEP
excelexcel-formula
Koba
Koba

Reputation: 1544

Paste into cell only if certain conditions are met

I am trying to create a list of emails that satisfy a certain condition. So, I have three sheets. In number 1 sheet I have a list of emails. In sheet number 2 I have a bigger list of emails. Now, I need to determine if emails in sheet number 1 are also in sheet number 2. If they are I want to create a list of those emails in sheet number 3.

Attempt. Assuming the two email lists are in the same sheet in columns A (small list) and B (big list). First, I determine whether the emails from smaller list are also in the bigger list. I use

=vlookup(A1,$B$1:$B$100,1,false)

Now, I am puzzled on how to set a function that will paste an email into cell if an email from smaller list is also in the bigger list. I thought of trying COUNTIF and IFERROR, but could not produce anything useful. The closest I got was

=if(vlookup(A1,$B$1:$B$100,1,false)=A1,1,0)

This gives me 1 when an email from smaller list is in the bigger list and #N/A when not cause there is nothing for vlookup. Now, instead of 1s and #N/As I need the emails themselves to be pasted in and IF function does not like ranges. Any suggestions please. Your help is appreciated.

Upvotes: 0

Views: 2002

Answers (2)

chuff
chuff

Reputation: 5876

For purpose of illustration, I assume that the small list is in the range A1:A25 in Sheet1 and the large list is in the range A1:A100 in Sheet2.

The following formula would be inserted into cell A1 of Sheet 3 and copied down to cell A25 of that sheet (i.e., the length of the small list).

=IFERROR(INDEX(Sheet2!$A$1:$A$100,SMALL(IFERROR(MATCH(Sheet1!$A$1:$A$25,Sheet2!$A$1:$A$100,0),""),ROWS(A$1:A1)),1),"-")

This is an array formula and needs to entered with the Control-Shift-Enter key combination.

Upvotes: 1

PW Kad
PW Kad

Reputation: 14995

Try the following

In Sheet 1 Cell A1 -

=if(iserror(vlookup(A1,$B$1:$B$100,1,false))=TRUE,0,1)

In Sheet 1 Cell A2 and down the rest of the page -

=if(iserror(vlookup(A1,$B$1:$B$100,1,false))=TRUE,A1,A1+1)

Make sure you do not use a static reference for A1 because we want it to change to A2 and so on on the way down the sheet.

In Sheet 3 Cell A1 -

=if(iserror(vlookup(1, 'Sheet1'!A:B,2,false))=TRUE,"",vlookup(1, 'Sheet1'!A:B,2,false))

In Sheet 3 Cell A2 and down the rest of the page -

=if(iserror(vlookup(2, 'Sheet1'!A:B,2,false))=TRUE,"",vlookup(2, 'Sheet1'!A:B,2,false))
=if(iserror(vlookup(3, 'Sheet1'!A:B,2,false))=TRUE,"",vlookup(2, 'Sheet1'!A:B,3,false))

Hide the column in Sheet 1 Column A where you are testing if it is in both sheets. This is just going through and testing whether it is in both lists and if it is not you are not incrementing the counter, therefore they won't show on sheet 3.

In sheet 2 you are essentially returning only a list in order of which E-mails match both conditions. That is why you need to increment the lookup value.

Upvotes: 1

Related Questions