在Excel中查找另一张表? [英] Lookup on another sheet in Excel?
问题描述
工作表1:
ID名称
1 Bob
2 Joe
3伯爵
表2:
名称ID无论
当我在表格2上输入一个名称以将其与表1中的名称进行匹配时,我希望填写表2中该字段中该行的id。因此,如果我在表格中键入Joe两个将在id列中填入2。
我的问题比这更复杂一些,但我相信这是现在的关键。
您可以使用VLOOKUP来查找密钥右侧的值。 INDEX和MATCH可用于查找键左侧的值。
在这种情况下,Sheet2上的公式将是
= INDEX(Sheet1!A:A,MATCH(A2,Sheet1!B:B,0))
pre>
I have a very simple Excel book that I am trying to spiffy up. Here's what it looks like:
Sheet 1: ID Name 1 Bob 2 Joe 3 Earl Sheet 2: Name ID Whatever
I would like when I type a name on sheet two for it to match it to a name in sheet 1 and fill in the id for that row in the field in sheet 2. So If I type "Joe" in sheet two it will fill in 2 in the id column.
My problem is slightly more complex than this, but I believe this is the crux of it for now.
解决方案You can use VLOOKUP to look up values that are to the right of the key. INDEX and MATCH can be used to look up values to the left of the key.
The formula in this case on Sheet2 would be
=INDEX(Sheet1!A:A,MATCH(A2,Sheet1!B:B,0))
这篇关于在Excel中查找另一张表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!