使用excel中的内置函数查找列中的最后一个匹配项 [英] find last match in column using built in functions in excel

问题描述

我有一个具有相同客户端ID的多个实例的excel工作簿.因此，一个客户端可以在列表中出现多次.我想找到客户端ID的最后一个(最接近底部)实例，以便随后可以在同一行中查找其他信息. VLOOKUP显示了第一个实例，我需要最后一个实例.

I have an excel workbook with multiple instances of the same client ID. So, a client can appear more than once in the list. I would like to find the last (closest to the bottom) instance of the client ID so i can then look along that same row for additional information. VLOOKUP shows the fist instance, I need the last.

那么，如何使用内置函数查找给定客户端ID的最后一个实例?我宁愿不写宏来做到这一点.

So, how do I find the last instance of a given client ID using built in functions? I'd rather not write a macro to do this.

推荐答案

假设您要查找范围A2:A8中ID "id_1"的最后一个实例，并从范围B2:B8返回对应的值，然后使用:

Suppose you want to find last instance of id "id_1" in range A2:A8 and return corresponding value from range B2:B8, then use:

=LOOKUP(2,1/(A2:A8="id_1"),B2:B8)

如果要返回ID "id_1"在范围A2:A8中的最后一个实例的 index ，请使用:

If you want to return index of last intance of id "id_1" in range A2:A8, use:

=MATCH(2,1/(A2:A8="id_1"))

具有数组项( CTRL + SHIFT + ENTER ).

with array entry (CTRL+SHIFT+ENTER).

如果要返回ID为"id_1"的最后一个实例的行号，请使用:

If you want to return row number of last intance of id "id_1", use:

=LOOKUP(2,1/(A2:A8="id_1"),ROW(A2:A8))

此语法是一个特殊技巧:(A2:A8="id_1")的计算结果为{TRUE,FALSE,FALSE,TRUE,...}.接下来，假设TRUE=1和FALSE=0，部件1/{TRUE,FALSE,FALSE,TRUE,...}为您提供{1,#DIV0!,#DIV0!,1,..}.

This syntax is a special trick: (A2:A8="id_1") evaluates to {TRUE,FALSE,FALSE,TRUE,...}. Next, assuming that TRUE=1 and FALSE=0, the part 1/{TRUE,FALSE,FALSE,TRUE,...} gives you {1,#DIV0!,#DIV0!,1,..}.

要返回结果数组lookup_value中的最后一个数字，应大于lookup_array中的任何值(在本例中为{1,#DIV0!,#DIV0!,1,..}).由于数组仅包含1或#DIV0!，因此我们可以将2用作lookup_value，因为它总是大于1.

To return last number in resulting array lookup_value should be greater than any value in lookup_array ({1,#DIV0!,#DIV0!,1,..} in our case). Since array contains only 1 or #DIV0!, we can take 2 as lookup_value, because it's always greater than 1.

这篇关于使用excel中的内置函数查找列中的最后一个匹配项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！