Excel查找值技巧，根据两个值来查找相对应的值

标签：Excel公式，VLOOKUP函数，OFFSET函数，SUMIFS函数

在Excel中，查找的需求多种多样，关键是看你怎么样搭配各种函数来实现了。如下图1所示，要根据代码和编号两个值来查找对应的数量。

图1

有三种解决方案来实现目的：

1.连接关键值。此时，可以使用辅助列，也可以使用数组公式。

2.SUMIFS函数。此时，返回的值必须是数字。

3.OFFSET函数。此时，如示例中的代码列排好序才能实现。

连接关键值

如下图2所示，在编号列和数量列之间插入一个辅助列，然后输入公式：

=A2 & "-" & B2

向下拖动复制到数据末尾。然后，在单元格G5中输入公式：

=VLOOKUP(G2 & "-" & G3,$C$2:$D$15,2,FALSE)

图2

当然，连接符号不一定是“-”，可以使用其它符号。也可以不使用连接符号。在最终的工作表中，你可以隐藏辅助列，看起来可能会更好。

如果你了解数组公式，可以不借助辅助列。如下图3所示，在单元格F5中输入公式：

=INDEX($C$2:$C$15,MATCH(F2 & "-" & F3,A2:A15 & "-" & B2:B15,0))

然后，按下Ctrl+Shift+Enter组合键。

图3

使用SUMIFS函数

如果返回的值是数字，则可以使用SUMIFS函数。如下图4所示，在单元格F5中输入公式：

=SUMIFS($C$2:$C$15,$A$2:$A$15,F2,$B$2:$B$15,F3)

图4

使用OFFSET函数

可以使用OFFSET函数返回需要查找的单元格区域，然后使用查找函数来查找相对应的值。如下图5所示，在单元格F5中输入公式：

=VLOOKUP(F3,OFFSET($B$1,MATCH(F2,A2:A15,0),0,COUNTIF(A:A,F2),2),2,FALSE)

图5

上述公式中，

MATCH(F2,A2:A15,0)

返回查找的第一个数值出现的位置。

COUNTIF(A:A,F2)

返回查找的第一个数值出现的次数，也就是要查找的数值区域的高度。

将上述两个返回值作为OFFSET函数的参数，返回要查找的单元格区域，作为VLOOKUP函数的参数，最后返回相对应的值。

当然，这样的公式也需要数值排序如示例一样。