将一个Excel工作表中的两列与另一工作表中的两列进行比较，如果匹配，则从另一列中复制数据 [英] Comparing two columns in one Excel sheet, to two columns in another sheet, and if they match, copy data from another column

问题描述

我一直在使用Excel VLOOKUP函数来完成此任务，但我对此并不熟悉.

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

我需要执行以下操作:

在第一页上，A列和B列包含3000行名字和姓氏.

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.

如果两列在工作表1和工作表2上完全匹配(例如A1 + B1 =工作表2 A7 + B7)，我需要将电子邮件地址从工作表2的C列复制到工作表1的C列.

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.

是否有VLOOKUP公式来完成此操作，或者这需要成为VBA脚本吗?

Is there a VLOOKUP formula to accomplish this, or is this going to need to be a VBA script?

推荐答案

尝试一下:

将此公式放在sheet1列C上:

Put this formula on sheet1 column C:

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

在sheet2上需要有4列，第一列必须是CONCATENATE FORMULA，例如:

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)

它是将sheet1上的名字和姓氏连接起来，并在A列的Sheet2上寻找它，如果存在匹配项，它将在sheet2列D(第D列的索引为4)中返回电子邮件单元格的值.

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).

希望对您有帮助

这篇关于将一个Excel工作表中的两列与另一工作表中的两列进行比较，如果匹配，则从另一列中复制数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！