Ron Rosenfeld Wrote:
On Mon, 20 Feb 2006 17:24:03 -0800, daikontim
wrote:

Hi,
I have 2 columns. Column A has numbers, some of which are repeated
numerous times. Column B has dates. How do I look up the nth
occurrence
of a particular number in column A and return the corresponding date?
Any
help greatly appreciated.

Try the array formula:

=INDEX(dts,LARGE((rng=num)*ROW(rng),COUNTIF(rng,nu m)+1-n))

entered by holding down <ctrl<shift while hitting <enter. Excel
will place
braces {...} around the formula.

rng is the range with numbers in Column A
num is the particular number you are searching for.
n is the occurrence number
dts is the range of dates in column B


--ron

Ron's solution is outstanding

Here is a small change that will allow the range definitions rng and
dts to be moved from row 1 - same instructions as in Ron's post

{=INDEX(dts,LARGE((rng=num)*ROW(rng),COUNTIF(rng,n um)+1-n)-MIN(ROW(rng))+1)}


Neill


--
neillcato
------------------------------------------------------------------------
neillcato's Profile: http://www.excelforum.com/member.php...o&userid=31750
View this thread: http://www.excelforum.com/showthread...hreadid=514606