CODEWITHSUNDEEP
excelexcel-formula
portalstudent
portalstudent

Reputation: 25

Excel vlookup but different

I have two tables. One 'master' data table and one log table (as shown in the screenshots)

Log dataset:

log dataset

'Master' dataset:

enter image description here

The result I want is a formula that gives me the email addresses of the people who do not appear in the log dataset. In this case,the result I want is:

enter image description here

Does anyone knows the formula to get this result?

Upvotes: 0

Views: 56

Answers (3)

David Richardson
David Richardson

Reputation: 147

The above answer should work. Here is another alternative that is similar.

enter image description here

=IF(XLOOKUP(D1, B:B, B:B, TRUE) = TRUE, E1, "")

If your version of Excel has FILTER(), you could then filer column G into a another column to get list of email addresses without the blank rows, or your can use a filter at the top of Col G.

Upvotes: 0

P.b
P.b

Reputation: 11468

=IFERROR(INDEX(Table4[email],MATCH(1,(COUNTIF(Table2[name],Table4[name])=0)*(COUNTIF($C$15:$C15,Table4[email])=0),0)),"")

Where Table4 = Master table Table2 = Log table

Enter the formula with ctrl+shift+enter

Upvotes: 0

crcvd
crcvd

Reputation: 1535

The result I want is a formula that gives me the email addresses of the people who do not appear in the log dataset

You can do a same-column VLOOKUP, searching for Name in Master in Name in Log. Wrap that call in ISERROR: when the resulting column is TRUE, that means the name does not exist in Log (this, of course, assumes no two e-mails share the same name).

Upvotes: 1

Related Questions