Compare two columns in one Excel sheet with two columns in another sheet, and if they match, copy data from another column

Question

I was looking to use the Excel VLOOKUP function for this, but I'm pretty unfamiliar with it.

I need to do the following:

On a sheet, one column A and column B contain 3,000 rows with first and last names.

I need to compare this with a second sheet, which also has first and last names with a third column containing email addresses.

If the two columns are exact matches on sheet 1 and 2 (e.g. A1 + B1 = Sheet2 A7 + B7), I need to copy the email address from column C on sheet 2, to column C on sheet 1.

Is there a VLOOKUP formula for this, or will it be the need for a VBA script?

Answer 1

try the following:

Place this formula in Sheet1 C column:

=VLOOKUP(CONCAT(A1,B1),Sheet2!A:D,4,0) 

You will need to have 4 columns in sheet2, the first column should be CONCATENATE FORMULA as follows:

 =CONCAT(B1,C1) 

The second column will be your first name, the third column will be your last name and the last column of the corresponding letter.

How does this formula work?

  =VLOOKUP(**CONCAT(A1,B1)**,Sheet2!A:D,4,0) 

It combines the first and last name on sheet1 and looks for it on Sheet2 in column A, if there is a match, it will return the value of the email cell in column column D2 (column index D is 4).

Hope this help helps you.

Answer 2

I would suggest a VBA script that uses an SQL query, perhaps something like this (to get the right result you need SQL language skills). In any case, this is an approach I would recommend for the advanced user:

 Dim oConn As ADODB.Connection, rs As ADODB.Recordset sWorkbookName = ThisWorkbook.FullName connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""" & sWorkbookName & """;Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX = 1""" sSheet1="myDataSheet1" sSheet2="myDataSheet2" oConn.Open connString 'just an example of SQL, you have to customize it sSQL = "SELECT [FIRST NAME], [LAST NAME],[EMAIL] FROM [" & sSheet2 & "$] " & " WHERE [FIRST NAME] + [LAST NAME} IN (SELECT [FIRST NAME] + [LAST NAME] FROM [" & sSheet1 & "$]" & ") ORDER BY [FIRST NAME] ASC" rs.Open sSQL, oConn, adOpenStatic, adLockOptimistic, adCmdText 'dump results on a temporary sheet ThisWorkbook.Worksheets("tmp_sheet").Range("A2").CopyFromRecordset rs rs.Close oConn.Close 

Answer 3

You can enter Sheet1!C1

 =INDEX(Sheet2!C:C,SUMPRODUCT(--(Sheet2!A:A=A1),--(Sheet2!B:B=B1),ROW(Sheet2!C:C)),0) 

and then copy it down.

This does not require:

• Using vba
• Using array formulas
• Using an additional (auxiliary) column.

The importance of this is missing.

More details

What you are looking for is usually called multiple search. There are a lot of questions about this at SO, and about many other articles elsewhere. I have compiled a list of such messages here.

There are many possible solutions for this. The one I found most reliable is shown here . This is what I used in this answer.

Answer 4

 =IF(AND(Sheet1.A1=Sheet2.A1, Sheet1.B1=Sheet2.B1),Sheet2.C1,'') 

Save this formula in column C of the target sheet.