3

I have a formula that I'm using in Excel to return the row # of the first item that matches a specified value.

Formula: =MATCH(0,COUNTIF($B$1,List),0) + CTRL + SHIFT + ENTER and in 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 the 2nd, 3rd, etc. instance 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 this outputs: 2

Ram
  • 3,092
  • 10
  • 40
  • 56
krumholz
  • 105
  • 1
  • 3
  • 10
  • This could help: http://stackoverflow.com/questions/9588286/match-or-vlookup-starting-from-the-end-of-the-range – assylias Apr 10 '12 at 16:12

2 Answers2

3

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

For 2nd try this array formula

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

replace the 2 with any value n for nth match. Assumes List is a single column

barry houdini
  • 45,615
  • 8
  • 63
  • 81
  • That fits like a glove with one small modification, an equals sign instead of the less than or greater than: =SMALL(IF(List=$B$1,ROW(List)-MIN(ROW(List))+1),2) – krumholz Apr 10 '12 at 18:06
  • @user1311882 - no problem - like I said I was trying to make it consistent with your original, if you match zero against the COUNTIF you are finding the position of the first cell that is `not equal` to $B$1 - to find the position of the first instance of $B$1 you would simply use `=MATCH($B$1,List,0)` – barry houdini Apr 10 '12 at 18:27
0

You can move the list dynamically every time an occurrence is found so that for the next occurrence the list will begin from 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>

please see this : filter dynamic

Ferdinand Musongela
  • 1