using match function for duplicate values

[enocht]

Hey guys am not that well versed with excel (beginner), but could use some help from all your expertise in excel.
I am trying to compare values from 2 sheets, using a match function, however, for duplicate values, only the first reference is given. with some help from the internet, i tried using the row() to make the value unique but am getting stuck.
The formula am using is: =IF(MATCH(F6,Sheet2!$E$6:$E$107, 0) = 'Sheet2!$F$6:$F$107,F6,"") for the (value if true) i want the reference of matched value(unique), and if false return blank or "-". Where F6:F107 of Sheet 2 has the Row() to identify the row.

If there is another way to get the result i need, i would be grateful.

It would really help me a lot if this could be solved.

Thanks in advance..

[NBVC]

Not sure what you mean... you want to know if the F6 value exists in E6:E107 of Sheet2? Want to know if it's there multiple times? Give example.

[enocht]

Well sorry for not being clear.. Well the F6 (Sheet 1) value appears in E6:E107 of Sheet2, it does appear multiple times, for which the match function returns the reference of the first match value found. I want it to give different reference for the same value (f7, f8. . . . .etc.) (i.e. the actual reference of that value in Sheet 2). I tried to uniquely identify with the row(), so that if the returned value = row() on true i get the reference of that value and not the same reference.
Would that clear your doubt ?

[NBVC]

This:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER and not just ENTER and copied down, will return from Sheet2!F6:F107 at the next matching point...

does that help you on your way? I am not sure what you are looking to return?

[enocht]

Well that didnt solve my purpose.. i'll give a simpler example:

Sheet 1

A B C row() =MATCH(B4,Sheet2!$C$3:$C$8,0)
1 500 3 1
2 200 4 7
3 300 5 3
4 500 6 1
5 900 7 #N/A
6 200 8 7
-------

Sheet 2

A B C
1 500
2
3 300
4
5 500
6 900

As per this example.. the match column doesnt give reference of duplicate values. What formula would be appropriate so that it gives the exact (unique) reference for even the duplicate values ?

Thanks.

[NBVC]

See attached.

I basically applied the formula I just provided above.. and added an error trap for items that don't appear as duplicates.. it returns blank on 2nd occurance...

Maybe I am misinterpreting your needs?

[enocht]

Well thanks a lot, your solution did seem to help a bit. However, i would like the reference of the 2nd occurrence as well. Also on applying this formula to my file, it seems that the values generated is giving the reference from the same sheet (Sheet 1 on which formula is applied), rather than from the other sheet.

Could you please help me out here. May be i am doing something wrong?

[enocht]

I was just wondering, would it not be possible to achieve the same results using a match, index function along with certain if statements to isolate the duplicate numbers with its different corresponding references ?

[NBVC]

Well thanks a lot, your solution did seem to help a bit. However, i would like the reference of the 2nd occurrence as well. Also on applying this formula to my file, it seems that the values generated is giving the reference from the same sheet (Sheet 1 on which formula is applied), rather than from the other sheet.

Could you please help me out here. May be i am doing something wrong?

That is what I have done, there are 2 500's on Sheet2, and I have matched each returning 2 different results. There is only 1 200 on Sheet2, so the second 200 on Sheet1 has nothing to match to.

I was just wondering, would it not be possible to achieve the same results using a match, index function along with certain if statements to isolate the duplicate numbers with its different corresponding references ?

Add a helper formula in Sheet2, C2:

Please Login or Register  to view this content.

then in Sheet1 Row 1:

Please Login or Register  to view this content.

copied down.. will yield same results.

[enocht]

THANKS A MILLION !!! You have helped me get past this "storm" with ease. Though i don't know much about excel, it sure is a wonderful application, one could almost do anything on it, if you know the basics and functions well. Would surely learn more of it now. Thanks a Lot ..!!!