Excel 2007 : Problem of using INDEX/MATCH function with duplicated values in two sheets

hi all
I have two sheet, in that both sheet has employee ids and the training names they have attended, and some of the employees has attended more than 2 to 5 trainings.
now the problem i want to check both the sheet for its machine correctness of data.

if i put vlookup then it takes first value and show that this employee has attended this training, but the same employee has attended more trainings.

how can i check this using index and match functions.

Please help me..

Added the sample excel sheet.

tocheck.xlsx

There is a sheet called "ToCheckData" under that Column D i need output. for example i have kept in sheet "Example"

We can help but you have to SHOW us what your end result need is. Don't dumb it down, show us your needed output. Click GO ADVANCED and use the paperclip icon to post up a sample copy of your actual workbook, replace sensitive data with fake data. Make sure the workbook manually demonstrates your desired results based on the sample data so we see the goal and the problem.

Originally Posted by mohiyuddinkhan

hi Beaucaire

i have already added the excel sheet to my post for which you have asked me to put.

request you to help me on this.

Problem of using INDEX/MATCH function with duplicated values in two sheets

FYI: The forum sends emails to notify us when a NEW POST is added to a thread in which we are involved. You needed to "ping" the thread with a new message, editing prior posts does not do that.
==============


1) On the RawData sheet, put this formula in E2, then copy down the the dataset:

=$A2 & "-" & $B2 & "-" & $D2

With that "unique key per row" from above, it's now simple to match things.

2) On the ToCheckData sheet, put this formula in D2 and copy down:

=IF(ISNUMBER(MATCH($A2&"-"&$B2&"-"&$C2, RawData!$E:$E, 0)), "Yes", "")

You can replace the "" with "WrongData" if you wish.

Even more informative, you could use this to get more than a "yes", how about the actual date of the found session:

=IFERROR(INDEX(RawData!$C:$C, MATCH($A2 & "-" & $B2 & "-" & $C2, RawData!$E:$E, 0)), "none")