代码之家 › 专栏 › 技术社区 › pedroteixeira07

有没有一种能识别重复数字的VLOOKUP?

vlookup offset excel-2010 excel-formula excel

1

pedroteixeira07 · 技术社区 · 9 年前

在excel选项卡的a列中有一个值列表,在旁边的B列中有单词列表。在另一个选项卡上,我试图从B中检索单词,其中A的值最小,然后是第二个较小的值,第三个,等等。

示例:列A将有(2,3,3,6,8)和B(汽车、狗、猫、房子、船)

我首先创建了一个列,在其中检索最小值:

然后一个VLOOKUP为我工作:

到现在为止,一直都还不错。我的问题是当我的价值观重复*即。: 在上面的示例中,每当函数在列A中找到值3时,我得到狗两次而不是狗和因为它只显示vlookup找到的第一个值。

我已尝试添加偏移: =OFFSET(SMALL('Table'!$A:$A,1),1,0) 但不确定它是否能从其他标签中提取。

1 回复 | 直到 9 年前

1

Tom Sharpe 9 年前

=INDEX(Table!B:B,MATCH(A2,Table!A:A,0)+COUNTIF(A$1:A1,A2))

您也可以使用类似这样的方法来获取最小值、次最小值等,而不是硬编码:-

=SMALL(Table!$A:$A,ROW(1:1))

好的,我将要使用的技巧是用相等的值分隔行,在每行中添加少量的值,使值唯一。以下是我的公式:-

=LARGE(IF(A$2:A$9="Restaurants",C$2:C$9),ROW(1:1))

要获得最大值,这是一个标准公式

=INDEX(D$2:D$9,MATCH(LARGE(IF(A$2:A$9="Restaurants",C$2:C$9+ROW(C$2:C$9)/10^7),ROW(1:1)),C$2:C$9+ROW(C$2:C$9)/10^7,0))

以获得匹配的城市。即使相邻行中没有相等的值,这也会起作用。

我不知道实际数据中有多少行,如果超过100行,则需要使用小于0.01的行。

请注意,这些是数组公式,需要输入 Ctrl键 转移 进来

对于最小的,只需将大变小。

如果您想排除任何非数值,如“未排名”,则需要

=INDEX(D$2:D$9,MATCH(LARGE(IF((A$2:A$9="Restaurants")*ISNUMBER(C$2:C$9),C$2:C$9+ROW(C$2:C$9)/10^7),ROW(1:1)),C$2:C$9+ROW(C$2:C$9)/10^7,0))

我不建议将其更改为使用D:D、C:C等,因为这会很慢,但会起作用。