在Excel中的列表中查找第n个匹配项 [英] Finding nth Match in a List in Excel

问题描述

我在A1:A5的Excel中有一张表

I have a table in Excel from A1:A5

a  
a  
b  
a  
b

我想使用Excel函数而不是VBA查找值'a'的第n次出现的行

I want to find the row of the nth occurrence of the value 'a' using Excel Functions and not VBA

• 如果我在此函数中输入1，我想得到1.

• If I put in 1 into this function, I want to get 1.

如果我在此函数中输入2，我想得到2.

If I put in 2 into this function, I want to get 2.

如果我在此函数中输入3，我想得到4

If I put in 3 into this function, I want to get 4

一种可行的解决方案是添加以下公式 =COUNTIF($A5:A5,"a")在B1中，然后将其向下拖动到B5.

A solution that works is to add the following formula =COUNTIF($A5:A5,"a") in B1 and then drag it down to B5.

然后我可以使用=MATCH(C1,B1:B5,0)，其中C1包含变量n.

I can then use =MATCH(C1,B1:B5,0) where C1 holds the variable n.

但是，有一种无需添加额外列的更整洁的方法吗?

However is there a neater way without having to add extra columns?

推荐答案

=AGGREGATE(15,6,1/(myMatch=myRange)*ROW(myRange),Occurrence)

公式的1/(..)部分将不匹配项转换为DIV/0!错误，AGGREGATE函数可以忽略该错误.因此，最小值将是第一个匹配值；等

The 1/(..) portion of the formula converts non-matches to DIV/0! errors, which the AGGREGATE function can ignore. So the Smallest value will be the first matching value; etc.

这篇关于在Excel中的列表中查找第n个匹配项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！