Match First and Last names on separate sheets in Excel

0
votes

I want to match first and last names which are in separate columns and in separate sheets in an excel file. Below is the sample of data:

Sheet 1:

First Name      Last Name

Kevin             Durant
Micheal           Jordan

Sheet 2:

First Name        Last Name
Mike              Ashley
Kevin             Durant

In the output, I need to get those first and last names which are in Sheet-2 but not in Sheet-1. I did the research and found this vlookup formula but I have not implemented it since I could not understand how to modify it:

=VLOOKUP(A2,'Names-2'!$A$2:$A$19,1,FALSE)

I found it from this link:vlookup

Any help would be great for me to understand vlookups. Thank you

1
excelexcel-formulavlookupspreadsheetlookup
Is the Michael on sheet 1 and Mike on sheet 2 actually different? For text, you will need exact matches (not case sensitive) – urdearboy
@urdearboy yes they are different names. – zain ul abidin
What's the desired output for your sample data? – Sangbok Lee
@SangbokLee in the output I need the first and last names that are in sheet-2 but not in sheet-1. output can be shown on sheet 1 or on sheet 2 where the vlookup is applied. – zain ul abidin
So you want Kevin Durant in this case? – Sangbok Lee

1 Answers

0
votes

Adding helper columns will be helpful.

  1. Fill C2:C999 on both sheets with =A2&" "&B2.
  2. Fill D2:D999 on Sheet2 with =IFERROR(MATCH(C2, Sheet1!C:C, 0), "no").
  3. Fill E2:E999 on Sheet2 with =IF(D2="no", C2, "").

The output column is E on Sheet2. Sample Excel file is here.