Excel MATCH + COUNTIF function: retrieving the nth value in an array

excel function countif excel-match
krumholz picture krumholz · Apr 10, 2012 · Viewed 18.8k times · Source

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

Answer

barry houdini picture barry houdini · Apr 10, 2012

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

Related questions

Excel VBA Countif between dates

I am needing to create a function to count the occurrences of an account number between two dates. The first date is based on the function input and the second is 3 months in advance (date may not be contained within …

Read More
How to return a result from a VBA function

How do I return a result from a function? For example: Public Function test() As Integer return 1 End Function This gives a compile error. How do I make this function return an integer?

Read More
What is the equivalent of "!=" in Excel VBA?

The problem is that != does not work as a function in excel vba. I want to be able to use If strTest != "" Then instead of If strTest = "" Then Is there another approach to do this besides !=? My function to mimic != …

Read More