将Excel中的另一列与另一列添加

发布于 2025-01-26 02:58:19 字数 348 浏览 3 评论 0原文

我在Excel中有2个电子表格。电子表格1包含许多列，包括SSN和员工ID。电子表格2包含不同的列，并具有SSN列，但没有员工ID。我想在电子表格2中创建一个称为员工ID的新列，并与该员工的SSN匹配它与电子表格1中相关的SSN。我觉得我需要使用vlookup，但我不确定。有帮助吗？上面的感谢

=VLOOKUP(L2, [spreadhseet1.xlsx]spreadhseet1!$A:$P, 2, FALSE)

是我使用的公式。 L2是表2中包含员工SSN的列。然后，我从AP列中获取了Table1中所有值的范围。 exprephSeet2中的第2列包含员工ID，这就是为什么我输入2。不确定为什么会提供错误

原文

I have 2 spreadsheets in excel. Spreadsheet 1 contains many columns including ssn and employee ID. Spreadsheet 2 contains different columns and has an ssn column but not the employee id. I wanted create a new column in Spreadsheet 2 that is called employee id and match it with the employee's ssn that it is correlated to in Spreadsheet 1. I feel like I would need to use VLOOKUP but I am not entirely sure. Any help ? Thanks

=VLOOKUP(L2, [spreadhseet1.xlsx]spreadhseet1!$A:$P, 2, FALSE)

Above is the formula that I used. L2 is the column in table2 that contains the employee's ssn. I then took the range of all values in table1 from column A-P. Column 2 in spreadhseet2 contains the employee id which is why I entered 2. Not sure why it is providing an error

分享到QQ

分享到微博

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

发布评论

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

揽清风入怀 2025-02-02 02:58:19

Vlookup是正确的功能。

如果您的源与

        [A]        [B]
    1]  SSN     |  empid
       ---------------------
    2]  123     |  ABC
    3]  456     |  DEF
....
99999]  987     |  QQQ   
                   ^------- index = 2 for return value
        ^------------------ lookup is in first column

“ Empid”相似并想填充目的地：

  [a]          [b]         [c]
1] ssn |    empid    |   col2    |    col 3 | ..
  ---------------------------------------------
2] 123 |             |    B      |      C   |
3] 456 |             |    d      |      e   |

            ^----- formula here
    ^------------- this value is used as lookup

然后将其放置在空的“ Empid”列中A公式

= vlookup（a2，'sheetname'！$ a2：$ a2：$ b9999，2，false ）

其中

A2是您的源列。 2显然同一行。

'SheetName'！$ a2：$ b99999是您的数据源区域，不包括标头。

2是您的1个指数列返回

false的是返回精确的匹配

通知，即您可能需要处理源数据中不存在查找值的情况。这将返回＃n/a和可以简单地处理

如果您绝对需要跨文件执行此操作，那是可能的，但是如果不存在文件，则可能会破坏某些内容。在此处进行解释。

nocese 如果您在excel的非US版本，函数可能与“”; and 具有不同的名称

VLOOKUP is the correct function.

If you have a source similar to

        [A]        [B]
    1]  SSN     |  empid
       ---------------------
    2]  123     |  ABC
    3]  456     |  DEF
....
99999]  987     |  QQQ   
                   ^------- index = 2 for return value
        ^------------------ lookup is in first column

and want to populate destination with "empid":

  [a]          [b]         [c]
1] ssn |    empid    |   col2    |    col 3 | ..
  ---------------------------------------------
2] 123 |             |    B      |      C   |
3] 456 |             |    d      |      e   |

            ^----- formula here
    ^------------- this value is used as lookup

Then you place in the empty "empid" column a formula

=VLOOKUP(A2, 'Sheetname'!$A2:$B9999 , 2 , false)

where

A2 is your source column. 2 obviously same row.

'Sheetname'!$A2:$B99999 is your data source area, excluding header.

2 is your 1-indexed column to return

False is to return a precise match

Notice that you may be need to handle a case where lookup value does not exist in source data. This will return #N/A and can be handled simply

If you absolutely NEED to do this across files, it's possible, but you risk breaking something if the files are not there. It's explained here.

NOTICE If you are on a non-US version of Excel, functions may separate with ";" and have different names

回复收藏 0 原文

~没有更多了~