我有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
发布评论