Here's my problem. I have an excel workbook with 2 worksheets in it. The
first has a list of numbers in column A. The 2nd worksheet has another
list of numbers in column B of that worksheet and I need to make a
formula that for each number in worksheet 1, it will search the numbers
in worksheet 2 and tell me where the second occurance of the number
appears. The situation is that I know that the number is in worksheet 2
at least one time, but I need to find the second occurance of the number
if it exists. Can anyone help?? Thanks

DLS

Example: Worksheet 1 Worksheet 2
1 1
2 3
3 8
4 1
5 5
I need to know the position and existance of the second number 1.

*** Sent via Developersdex http://www.developersdex.com ***

<DLS wrote...
....
Example: Worksheet 1 Worksheet 2
1 1
2 3
3 8
4 1
5 5
I need to know the position and existance of the second number 1.

If the worksheet 1 range started in cell A1, then the row index of the 2nd
instance of Worksheet1!A1 in Worksheet2!B:B (which I'll name WS2B) would be
given by

C1:
=IF(COUNTIF(WS2B,A1)1,
MATCH(A1,OFFSET(WS2B,MATCH(A1,WS2B,0),0),0)+MATCH( A1,WS2B,0),
"no second instance")

Fill down as needed.

Finally got it working. Problem was pilot error here. Thanks a million
for the help. Your formula does the job.

DLS

*** Sent via Developersdex http://www.developersdex.com ***