MATCH Function based on specified occurence (i,e return position based on 2nd occurence)

  • Thread starter Thread starter terryspencer2003
  • Start date Start date
T

terryspencer2003

I have an array of variables (rows). I want to query the position of
a variable in an excel array. However the array is large and has this
variable in it several times. Normally I would use a MATCH function
to find the position of the variable in question:

MATCH(variable, ARRAY, 0)

I can only pick the first occurence or the last occurence by using a 0
or a 1 in the last arguement of the MATCH statement.

Is it possible to return the position of the variable based on a
specified occurence. That is I know it occurs 8 times and I want the
position of the third occurence within the array.

What function would I use?

Thanks

TS
 
I can only pick the first occurence or the last occurence by using a 0
or a 1 in the last arguement of the MATCH statement.

Is it possible to return the position of the variable based on a
specified occurence. That is I know it occurs 8 times and I want the
position of the third occurence within the array.
Click to expand...

The index of the Nth instance of LookValue from the 'beginning' of Array
searching 'forward' is given by the array formula

=IF(COUNTIF(Array,LookupValue)>=N,
SMALL(IF(Array=LookupValue,ROW(INDIRECT("1:"&COUNTA(Array)))),N),
#N/A)

The Nth instance from the 'end' of Array searching 'backward' is given by
the array formula

=IF(COUNTIF(Array,LookupValue)>=N,
LARGE(IF(Array=LookupValue,ROW(INDIRECT("1:"&COUNTA(Array)))),N),
#N/A)

Dispense with the IF() wrapper and use just the SMALL or LARGE function
calls if you can accept the #NUM! error value when there are fewer than N
instances of LookupValue in Array.
 
Harlan Grove said:
=IF(COUNTIF(Array,LookupValue)>=N,
SMALL(IF(Array=LookupValue,ROW(INDIRECT("1:"&COUNTA(Array)))),N),
#N/A)
Click to expand...
....

Doesn't work for arrays per se (not ranges). Change that to

=IF(SUMPRODUCT(--(Array=LookupValue))>=N,
SMALL(IF(Array=LookupValue,ROW(INDIRECT("1:"&COUNTA(Array)))),N),
#N/A)

if you're dealing with arrays rather than ranges.
 
Back
Top