Vlookup：如何将一列与另一列匹配并加入它们而不会丢失数据？

发布于 2025-02-03 05:19:37 字数 928 浏览 4 评论 0原文

我有两张看起来像这样的纸：

Sheet1

id | phone | age
0    123     23
1    456     42
2    789     36

Sheet2
id | city | country
0   madrid  spain
1   nyc     usa
2   dubai   uae 
3   london  england
4   lisbon  portugal

我的目标是要有一个看起来像这样的纸：

Sheet3
id | phone | age | city | country
0    123     23   madrid  spain  
1    456     42   nyc     usa
2    789     36   dubai   uae
3                 london  england
4                 lisbon  portugal

我一直在使用这种公式：

=ARRAYFORMULA({'Sheet1'!A$1:C$4, VLOOKUP('Sheet1'!A$1:A$4,{'Sheet2'!A$1:A$6, 'Sheet2'!B$1:C$6}, {2,3}, false)})

这就是我得到的：

Sheet3
id | phone | age | #N/A | #N/A
0    123     23   madrid spain
1    456     42   nyc    usa 
2    789     36   dubai  uae

因此，如您所见，它正在省略从列的标题组合表中的Sheet2，它丢弃了ID不匹配的任何行。我该如何告诉它将这些行放入并将细胞留空，并包括Sheet2的列标题？

原文

I have two sheets that look something like this:

Sheet1

id | phone | age
0    123     23
1    456     42
2    789     36

Sheet2
id | city | country
0   madrid  spain
1   nyc     usa
2   dubai   uae 
3   london  england
4   lisbon  portugal

My goal is to have a sheet that looks like this:

Sheet3
id | phone | age | city | country
0    123     23   madrid  spain  
1    456     42   nyc     usa
2    789     36   dubai   uae
3                 london  england
4                 lisbon  portugal

I've been using this formula:

=ARRAYFORMULA({'Sheet1'!A$1:C$4, VLOOKUP('Sheet1'!A$1:A$4,{'Sheet2'!A$1:A$6, 'Sheet2'!B$1:C$6}, {2,3}, false)})

This is what I get:

Sheet3
id | phone | age | #N/A | #N/A
0    123     23   madrid spain
1    456     42   nyc    usa 
2    789     36   dubai  uae

So as you can see, it is leaving out the column headers from Sheet2 in the combined table and it leaves out any rows where the id doesn't match. How do I tell it to leave those rows in and leave the cells blank and include the column headers from Sheet2?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

抱猫软卧 2025-02-10 05:19:37

尝试：

=ARRAYFORMULA(QUERY({A:C, IFNA(VLOOKUP(IF(A:A<>"", A:A, "×"), E:G, {2, 3}, 0)); 
 FILTER({E2:E, IFERROR(E2:F/0), F2:G}, NOT(COUNTIF(E2:E, A2:A)))}, 
 "where Col1 is not null order by Col1", 1))

try:

=ARRAYFORMULA(QUERY({A:C, IFNA(VLOOKUP(IF(A:A<>"", A:A, "×"), E:G, {2, 3}, 0)); 
 FILTER({E2:E, IFERROR(E2:F/0), F2:G}, NOT(COUNTIF(E2:E, A2:A)))}, 
 "where Col1 is not null order by Col1", 1))