Clever Geek Handbook

Excel function MATCH + COUNTIF: get nth value in array

I have a formula that I am using in Excel to return the string # of the first item that matches a specified value.

Formula: =MATCH(0,COUNTIF($B$1,List),0)

+ CTRL+ SHIFT+ ENTERand on Mac: CMD+RETURN

Becomes:

MATCH(0,{**0**;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0},0)

Outputs: 1

I would like to update the function so that I can find instance 2, 3, etc. that matches the specified value.

Like this:

MATCH(0,{0;**0**;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0},0)

And these are the outputs: 2

+3


source to share


2 answers


Doesn't your current formula find the position of the first value in the list that doesn't match B1?

For the second try this array formula



=SMALL(IF(List<>$B$1,ROW(List)-MIN(ROW(List))+1),2)

replace 2

with any value of n for the nth match. Assumes List is one column

+3


source


You can move the list dynamically each time an occurrence is found, so that for the next occurrence, the list starts at the last position found.

use this MATCH(<Match value>,INDIRECT(" <column of data> " & <Last position found> +1 & ":<Column of Data><Last Row Of Data>"),0)+<Last position found>



see this: filter dynamic

0


source






More articles:


All Articles