在两个工作簿的几列中进行VLOOKUP [英] VLOOKUP across several columns in two workbooks

问题描述

我有2本工作簿.

第一个被称为 June2122.xls

它包含诸如 Last Name (B2:B300)， Firstname (C2:B300)之类的列...

It has columns such as Last Name (B2:B300), First Name (C2:B300), etc...

第二个是 June Emails.xls

它具有诸如 Last Name (B2:B300)， Firstname (C2:C300)， Email Address (D2:D300).这三列的名称范围为 Data_Table

It has columns such as Last Name (B2:B300), First Name (C2:C300), Email Address (D2:D300). These three columns are in a name range called Data_Table

我需要做的是将 June2122.xls 中的名字和姓氏(B2:C2)与进行比较 June Emails.xls 中的名字"和"<姓氏> "(B2:C2).如果它们匹配，那么我需要将 June Emails.xls (D2)中的电子邮件地址填充到 June2122.xls

What I need to do is compare the First Name and Last Name (B2:C2) from June2122.xls with the First Name and Last Name (B2:C2) from June Emails.xls. If they match then I need to populate the email address from June Emails.xls (D2) into a new cell on June2122.xls

所有单元格的格式均设置为常规".

All cells are formated as "General".

我尝试了几个公式，最接近的是这个公式:

I've tried several formulas, the closest I've got was this one:

= IF(ISNA(VLOOKUP(B2，'June Emails.xls'！Data_Table，2，TRUE))，0，VLOOKUP(C2，'June Emails.xls'！Data_Table，3，TRUE))

我所收到的只是电子邮件应填入的单元格中的"FALSE".

and all I get is a "FALSE" in the cell the email should be populating in.

有人可以帮忙吗?提前非常感谢！

Can anyone help with this? Thanks very much in advance!

推荐答案

我知道这是一个老话题，但我刚刚发现堆栈溢出，

Hi I know this is an old topic but I have just found Stack overflow,

以下公式将基于串联值进行查找，而无需添加其他串联字段

The following formula will do a lookup based on concatenated Values without a need for an additional concatenated field to be added

=IFERROR(INDEX(Sheet1!C$2:C$7,MATCH((A2&B2),(Sheet1!A$2:A$7&Sheet1!B$2:B$7),0),0),0)

** NB:这是一个数组公式，因此请记住使用Ctrl-Shift-Enter使其起作用:)

**NB: This is an Array Formula so remember to use Ctrl-Shift-Enter to get it to work :)

所以您可以看到每个部分所引用的内容(抱歉，我使用了我自己的虚拟数据)，这里是两个数据表

so you can see what each part is referencing (sorry I used my own dummy data) here are the two data tables

上面的公式在Sheet2的单元格C2中使用，正在查找Sheet2！A2&的值.B2与Sheet1的A列和B列

The Formula above is used in cell C2 of Sheet2 and is looking up the values of Sheet2!A2 & B2 against Sheet1 column A and B

弄清楚这个公式为我节省了很多时间，希望对您有帮助:)

Figuring out this formula has saved me days of work, I hope it helps :)

这篇关于在两个工作簿的几列中进行VLOOKUP的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！