两列查询 [英] Two Column Lookup
问题描述
在一份工作表中,我有一个人:
另一方面,我有一张年,姓名和数字的表格
我正在尝试对加入的年份和名称进行查找,并返回第二个表中的给定数字。例如,2013Andrew McCutchen将返回8.2,2014年Andrew McCutchen将返回6.8。
目前,我只收到#N / a值与以下
= INDEX('2006 Results'!C2:C556,MATCH($ J $ 1& C3,'2006 Results'!$ A $ 2& $ B $ 556,0))
但是,我知道一些值在表中,因为我使用if语句进行测试,以确保我的拼写正确,任何指导都将不胜感激。
在 match
表达式,您将一个连接值 $ J $ 1& C3
与另一个单一连接值'2006 Results'!$ A $ 2& $ B $ 556 。匹配
期望第二个参数为范围而不是一个单一的值。
在这种情况下,需要多个条件,我更喜欢使用 sumifs
而不是 index-match
,即使t他的意图是回报单一价值。我认为 = SUMIFS('2006 Results'!$ C:$ C,'2006 Results'!$ A:$ A,j $ 1,'2006 Results'!$ B:$ B,$ c3)
将给出您需要的内容,并将其正确复制到该表格中的其他单元格。
I have a data set that I want to return an indexed column using two values: a year and a name. Both these values are formatted to general (I also tried text) in my spreadsheet.
In one work sheet I have a like of people:
On the other, I have a table of Years, Names, and a number
I am trying to do a lookup on the joined year and name and return the given number in the second table. For instance 2013Andrew McCutchen would return 8.2, and 2014Andrew McCutchen would return 6.8.
Currently, I only get the #N/a value with the following"
=INDEX('2006 Results'!C2:C556,MATCH($J$1&C3,'2006 Results'!$A$2&$B$556,0))
But, I know a certain value is in the table though because I have tested with an if statement to make sure my spelling is correct. Any guidance would be much appreciated.
In your match
expression, you are comparing one concatenated value $J$1&C3
to another single concatenated value '2006 Results'!$A$2&$B$556
. Match
expects that second parameter to be a range rather than a single value.
In cases like this, where multiple criteria are required, I prefer to use sumifs
rather than index-match
, even though the intention is to return a single value. I think =SUMIFS('2006 Results'!$C:$C,'2006 Results'!$A:$A,j$1,'2006 Results'!$B:$B,$c3)
will give what you need and should correctly copy to the other cells in that table.
这篇关于两列查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!