How to join two worksheets into one based on the data they contain

Are you really using Excel 2003? There are some functions that were only introduced for XL 2007, so we would have to avoid using those.

Pete

Hello Pete,

In my office computer I have only XL2003 installed, but if that hinders the solution to my problem I could relocate to another computer!

No, that's okay. In the attached file I've done it a bit differently than what you asked for - put this formula in F2 of the Corrective Actions sheet:

=IF(A2="","-",A2&"_"&COUNTIF(A$2:A2,A2))

then copy down to beyond your data to ensure that you pick up all the data (the hyphens indicate how far you have copied it). Then in the Defects sheet you could put this in E2 (or indeed in D2 - I just left a blank column there):

=IF(ISNA(MATCH($A2&"_1",'corrective actions'!$F:$F,0)),"",INDEX('corrective actions'!B:B,MATCH($A2&"_1",'corrective actions'!$F:$F,0)))

This can then be copied into F2:G2, with appropriate headings in row 1. This will get the first corrective action for that defect number - very similar formula can be used to get the second and third corrective actions by just changing the _1 to _2 and _3 in the formula. Then that row of formulae can be copied down to complete your table.

Hope this helps.

Pete

Pete thank you for your help!
In my post I neglected to tell you that I have many more columns than those in the attached file. This means, that I cannot have the corrective actions side-by-side..
I will consider this solution, though, if I will not come up with something else