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
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
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