在Excel中匹配两个表中的值 [英] Match values from two tables in excel

问题描述

一个表具有策略编号和编写每个策略的代理.代理人的名字多次.

One table has policy numbers and the agent who wrote each policy. An agent's name multiple times.

另一个表仅包含策略编号，我想在编号旁边添加代理的名称.

Another table has just the policy numbers and I want to add the agents' names next to the numbers.

VLOOKUP不起作用，因为firs表多次显示座席的姓名.

VLOOKUP doesn't work because the firs table shows the agents' names multiple times.

任何想法如何做到这一点?

Any ideas how to do this?

推荐答案

如果数字是唯一的，并且只有座席是重复的，则可以使用索引/匹配或Vlookup.
带有索引/匹配项:

If the numbers are unique and only the Agents are duplicates, you can use Index/Match or Vlookup.
With Index / Match:

=IFERROR(INDEX(Policy!$B$2:$B$15,MATCH(A2,Policy!$A$2:$A$15,0)),"")

策略是具有数字(A列，A2:A5列)和相应的Agent(B列，B2:B15列)的表，将引用更改为与最后一行相对应，但保留$作为固定引用.您可以将其向下拖动到A2旁边，以找到他们的探员所需的号码.

Policy is the sheet where you have Numbers (Column A, A2:A5)and corresponding Agent (Column B, B2:B15) change the references to correspond the last row but keep $ for fixed references. You can drag it down next to A2 where the numbers that you need to find their Agent are.

使用Vlookup:

=IFERROR(VLOOKUP(A2,Policy!$A$2:$B$15,2,FALSE),"")

您可以将其向下拖动到新表中数字所在的A2旁边.
还要更改Policy！$ A $ 2:$ B $ 15以对应您数据的引用.
2从第二列(Agent)返回值
完全匹配为False.

You can drag it down, next to A2 where the numbers are in the new sheet.
Also change Policy!$A$2:$B$15 to correspond the references of your Data.
2 to return the value from the second column (Agent)
False for exact match.

这篇关于在Excel中匹配两个表中的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！