在两张纸上使用Excel VLOOKUP()函数 [英] Using Excel VLOOKUP() function across two sheets
问题描述
我在Excel工作簿中有两张图纸，并且想要使用VLOOKUP()函数在Sheet2中搜索表格并将结果返回给Sheet1.我该怎么办?
I have two sheets in an Excel workbook and want to use the VLOOKUP() function to search a table in Sheet2 and return the result to Sheet1. How might I do this?
数据如下:
Sheet1
A B
1 id name
2 111 Jacob
3 102 david
4 110 John
Sheet2
A B
1 id Cell
2 111 03563334879
3 102 03563331234
4 110 03563334222
推荐答案
VLOOKUP上的简短指南，希望对您有所帮助.
Short guide for you on VLOOKUP, hope it helps.
VLOOKUP的语法是
VLOOKUP(Lookup_Value,Table Array,Col_index_num,Range_lookup)
OR，以单元格C2类型开始
OR, to start in cell C2 type
-
=VLOOKUP( - 然后单击单元格A2(此行要查找的值)
- 然后输入逗号
- 然后excel允许您单击另一张表以从sheet2中选择表，因此该范围为Sheet2！A2:B4
- 逗号
- 您要从查找数组中返回第二行，所以键入2
- 逗号
- 您需要完全匹配，因此输入FALSE
- 将方括号括起来并按Enter键
=VLOOKUP(- Then click cell A2 (The value to lookup for this row)
- Then type a comma
- Then excel allows you to click on another sheet to select the table from sheet2, so this would be range Sheet2!A2:B4
- comma
- You want to return the second row from your lookup array so type 2
- comma
- You want an exact match so type FALSE
- close brackets and hit enter
这适用于单元格C2，但是如果使用单元格右下角将其向下拖动，则它将在随后的行中失败，因为表数组引用是相对的，并且将向下移动的每个单元格将查找表引用向下移动一个.您要使查找表数组固定(不是正确的术语)，单击它们中的每一个(A2和B4)，然后按F4，或仅添加"$"，如下所示.这样可以修复行和列引用，以便在您向下复制时不会更改这些引用.
This works for cell C2 but if you drag it down using the cell bottom right corner it will fail in subsequent rows because the table array references are relative and it will move the lookup table reference down one for every cell you move down. You want to make the lookup table array fixed (not the right term) click in each of them (A2 and B4) and press F4, or just add the "$" as below. This fixes the Row and column reference so that when you copy down it will not change these references.
=VLOOKUP(A2,Sheet2!$A$2:$B$4,2,FALSE)
现在，您可以向下拖动以复制公式.
Now you can drag down to copy the formula.
这篇关于在两张纸上使用Excel VLOOKUP()函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
