你可以使用VLookup函数将来自两个工作表的数据匹配到第三个工作表上。

huangapple go评论57阅读模式
英文:

How can I use VLookup to match data from 2 sheets onto a 3rd sheet?

问题

On sheetA,我有一个姓名的列。在sheetB上,我有一个列,其中将这些姓名与ID配对。

在sheetC上,我想要有一个新的列,它从sheetA获取姓名,将其与sheetB上的姓名匹配,并返回相关的ID。

我一直试图使用vlookup来实现这一点,但还没有完全弄清楚。到目前为止,在sheetC上,我有:

=xlookup(A3,SheetA!$A$1:$A$600,SheetB!$B$1:$B$600,"not found")

我想要的结果如下:

sheetA

A
John Smith
Jane Doe
Joe Blow
John Smith
John Smith

sheetB

A B
John Smith 0001
Jane Doe 0002
Joe Blow 0003

sheetC - 期望的结果

A
0001
0002
0003
0001
0001
英文:

On sheetA I have a column of names. On sheetB I have those names in a column paired with an ID.

On sheetC I want to have a new column that takes the names from sheetA, matches them with the names on sheetB and returns the relevant ID.

I've been trying to achieve this with vlookup but can't quite figure it out. So far on sheetC I have:

=xlookup(A3,SheetA!$A$1:$A$600,SheetB!$B$1:$B$600,"not found")

The result I want is as follows:

sheetA

A
John Smith
Jane Doe
Joe Blow
John Smith
John Smith

sheetB

A B
John Smith 0001
Jane Doe 0002
Joe Blow 0003

sheetC - desired outcome

A
0001
0002
0003
0001
0001

Would anyone be able to point me in the right direction?

答案1

得分: 1

你查找值 A3 似乎不正确。尝试使用以下公式:

=XLOOKUP(SheetA!A1,SheetB!$A$2:$A$2000,SheetB!$B$2:$B$2000)

英文:

You lookup value A3 seems incorrect. Try-

=XLOOKUP(SheetA!A1,SheetB!$A$2:$A$2000,SheetB!$B$2:$B$2000)

你可以使用VLookup函数将来自两个工作表的数据匹配到第三个工作表上。

huangapple
  • 本文由 发表于 2023年3月23日 09:31:08
  • 转载请务必保留本文链接:https://go.coder-hub.com/75818568.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定