-1

I've been looking at using the Excel VLOOKUP function to accomplish this, but I'm pretty unfamiliar with it.

I need to do the following:

On sheet one, column A and column B contain 3000 rows of first names and last names.

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

If the two columns are an exact match on sheet 1 and sheet 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 to accomplish this, or is this going to need to be a VBA script?

user3708356
  • 39
  • 1
  • 6
  • I would do it by 1) Adding an extra column to each sheet that combines the names, and 2) Using a VLOOKUP with the new columns. – dev1998 Jan 23 '18 at 19:35

4 Answers4

2

try this:

Put this formula on sheet1 column C:

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

You would need to have 4 columns on sheet2, the first column would need to be a CONCATENATE FORMULA like this:

=CONCAT(B1,C1)

The second column would be your first name, third column your last name and last column the corresponding email.

How this formula work's?

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

It's concatenating the first and last name on sheet1 and looking for it on Sheet2 on column A, if there's a match it will return the email cell value in sheet2 column D (column D index is 4).

Hope this help you

warner_sc
  • 848
  • 6
  • 13
1

I would suggest a VBA script that uses an SQL query, maybe something like this (you need some SQL language skills in order to get the right result). Anyways, this is the approach I would recommend for an 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
  • I don't think the questioner has any data in a SQL database. – dev1998 Jan 23 '18 at 19:52
  • Please be aware that you CAN RUN SQL QUERIES against an Excel table, in Excel VBA. Just need to add a reference to Microsoft ActiveX Data Objects 6.1 Library. But this is only for advanced users however, that also know SQL language –  Jan 23 '18 at 20:16
  • I did not know that. Thanks. – dev1998 Jan 23 '18 at 20:20
1

You can enter in Sheet1!C1

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

and then copy downwards.

This does not require:

  1. Using VBA
  2. Using array formulas
  3. Using an additional (helper) column.

The importance of this is out of scope here.

More details

What you are looking for is typically called Multiple Lookup. There are quite a few questions about it in SO, and many other articles elsewhere. I have compiled here a list of such posts.

There are many possible solutions for that. The one I found most robust is shown here. This is what I used in the present answer.

Community
  • 1
  • 1
sancho.s ReinstateMonicaCellio
  • 14,708
  • 20
  • 93
  • 185
0
=IF(AND(Sheet1.A1=Sheet2.A1, Sheet1.B1=Sheet2.B1),Sheet2.C1,'')

Save this as formula to the Column C of the target sheet.

Salek
  • 293
  • 1
  • 11
  • This looks good if the names are exactly the same on both sheets. They have to be matched up in same rows too. – dev1998 Jan 23 '18 at 20:29