vlookup 查找匹配项并返回行中的另一个元素 [英] vlookup to find match and return another element in row
问题描述
我有两张纸.两张工作表的第一列都有用户 ID，但两张工作表之间只有一些 ID 重叠.我想将用户 ID 保留在第一张工作表中，但在第二张工作表中，第二列有一个我想要的数据点.对于第一个工作表中的那些用户 ID 也在第二个工作表中，我想获取这些数据.
i have two sheets. first column of both sheets has userids, but there is only some overlap of ids between the two sheets. i want to keep the userids in the first sheet, but in the second sheet, the second column has a point of data that i want. for those userids in the first sheet that are also in the second sheet, i want to get this data.
所以，比如第一张表中第一行的用户 ID，我如何使用 vlookup 在第二张表中找到相同的用户 ID(如果存在)，获取该匹配项的第二列的值，并将其带上回到第一张表的第二列?
so, for say the first row's userid in the first sheet, how could i use vlookup to find that same userid in the second sheet (if it exists), get the value of the second column of that match, and bring it back to the second column of the first sheet?
谢谢
推荐答案
修改此公式并将其放入第一个工作表上第二列的第一个单元格中.然后将其复制并粘贴到列中:
Modify and put this formula into the first cell of the second column on the first worksheet. Then copy and paste it down the column:
=VLOOKUP(A1, Sheet2!A$1:B$100, 2, FALSE)
=VLOOKUP(A1, Sheet2!A$1:B$100, 2, FALSE)
我们来看看这个函数的参数:
Let's look at the parameters for this function:
A1:此工作表上的该值是我们在下一个参数给出的范围内搜索的值.当您将整个公式复制并粘贴到列中时，它会随着每一行增加行 #.在第 2 行，将修改为 A2，依此类推.
A1: This value, on this worksheet, is what we're searching for in the range given in the next parameter. When you copy and paste the entire formula down the column, it increments the row # with each row. In row 2, it will be modified to A2, and so on.
Sheet2!A$1:B$100 :这是我们感兴趣的范围，位于第二个工作表上.它是从左上角到右下角的单元格.$ 符号告诉 Excel 在您将公式复制并粘贴到列中时不要更改行 #.修改 B$100 以适合您感兴趣的数据范围……如果第二张纸上只有 30 行 id，则类似于 B$30.
Sheet2!A$1:B$100 : This is the range that we are interested in, on the second worksheet. It is the top left to bottom right cell. The $ symbol tells Excel not to change the row #'s when you copy and paste the formula down the column. Modify B$100 to fit the range of data you are interested in... something like B$30 if you only have 30 rows of id's on the 2nd sheet.
2:这是您对检索值感兴趣的列，相对于上述参数.在这种情况下，2 对应于 B 列.
2: This is the column you are interested in retrieving the value, relative to the above parameter. In this case, the 2 corresponds with column B.
False:指示 Excel 查找完全匹配项.
False: This instructs Excel to find the exact match.
这篇关于vlookup 查找匹配项并返回行中的另一个元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
