vba 没有唯一 ID 的名字和姓氏的 VLOOKUP
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7836516/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VLOOKUP on first and last name with no unique ID
提问by user1005237
Situation:
情况：
I have two sheets: The first sheet contains a list of employee names; the second sheet contains a piece of a pay report for employees. I need to pull the employee pay data from sheet 2 to sheet 1 by searching for the employee on sheet 2. What complicates this is the report on sheet 2 contains changing employees, pay, and has no unique ID.
我有两张工作表：第一张工作表包含员工姓名列表；第二张表包含一份员工薪酬报告。我需要通过在工作表 2 上搜索员工来将员工工资数据从工作表 2 拉到工作表 1。使这变得复杂的是工作表 2 上的报告包含变化的员工、薪酬，并且没有唯一的 ID。
I am looking to fill in column A. I cannot permanently modify this sheet as it affects downstream reporting.
我希望填写 A 列。我无法永久修改此表，因为它会影响下游报告。
I split column B which was previously first name, middle initial (if any), last name. Note, about 10% of the names may have a different last name. For example, sheet 1contains the name Tara Feather, but the second report uses her maiden name of Tara Bird.
我拆分了以前是first name, middle initial (if any), 的B 列last name。请注意，大约 10% 的姓名可能具有不同的姓氏。例如，工作表 1 包含名称 Tara Feather，但第二份报告使用她的娘家姓 Tara Bird。
Attempts so far:
迄今为止的尝试：
I am new to Excel, and am just learning VLOOKUP. My first attempt was to pull the data based on the last name column on sheets 1 and 2, and enter data. Unfortunately, while that worked for 90% of the entries, the last 10% had the wrong values and the audit to correct took more time than manual entry.
我是 Excel 新手，正在学习VLOOKUP. 我的第一次尝试是根据工作表 1 和 2 上的姓氏列提取数据，然后输入数据。不幸的是，虽然这对 90% 的条目有效，但最后 10% 的值有误，并且比手动输入花费更多的时间进行审计来纠正。
Possible Ideas:
可能的想法：
My next thought was to combine an Ifstatement and VLOOKUPso that if last namematched, and first namematched, then execute the VLOOKUPand pull values, or enter "ERROR".
我的下一个想法是组合一个If语句，VLOOKUP以便如果last name匹配和first name匹配，则执行VLOOKUP并拉取值，或输入“错误”。
With this new thought, I don't know if I'm now in macro territory or if there are a combination of functions that can help. Ideally, the more it can do with 100% accuracy the better. However, if it can highlight the names it completed on sheet 2, I can always enter the non-highlighted manually, still saving time.
有了这个新想法，我不知道我现在是否处于宏观领域，或者是否有可以提供帮助的功能组合。理想情况下，它可以以 100% 的准确度做的越多越好。但是，如果它可以突出显示它在工作表 2 上完成的名称，我总是可以手动输入未突出显示的名称，仍然可以节省时间。
What are your thoughts and direction?
你的想法和方向是什么？
采纳答案by paulmorriss
You want 100% accuracy, but if someone has different names on the two sheets, then that's never going to work.
你想要 100% 的准确度，但如果有人在两张纸上有不同的名字，那么这将永远行不通。
So to get 90% of it right:
因此，要做到 90% 正确：
On the second sheet put this formula in G2 and copy it down for as many rows as you have names.
在第二张纸上，将此公式放在 G2 中，然后将其复制到与您的姓名一样多的行中。
=B2&D2
Put this formula in H2 and copy it down
把这个公式放在 H2 中并复制下来
=F2
On the first sheet put this formula in A2 and copy it down
在第一张纸上将此公式放在 A2 中并将其复制下来
=VLOOKUP(D2&C2,'second sheet'G:H,2,false)
Any rows where it can't find a matching firstname and surname will have #N/A in them.
任何找不到匹配的名字和姓氏的行都将包含 #N/A。
Assuming you can modify the second sheet permanently you can put the right surnames in so that the formulae work. You could also put in the middle names into the formulae in case you have two people with the same names.
假设您可以永久修改第二个工作表，您可以输入正确的姓氏，以便公式起作用。如果您有两个同名的人，您也可以将中间名放入公式中。
