Vlookup date by employee number and qualification at the same time

[johannsutherland]

Hi guys,

I’ve got a question involving a vlookup and perhaps a match / index / offset formula. Here goes:

I need to lookup the date that a certain employee (from a list including employee numbers) achieved a certain qualification. The report I’m working off lists each employee’s name and a specific qualification and a date in the columns next to it. All the employee’s subsequent qualifications are listed under each other, but luckily his name is also listed next to the qual and date. Each employee in my organisation is listed in the same report and they are all listed underneath each other.

Here is the example: Sheet A
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { font-weight: 700; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(184, 204, 228); }.xl64 { border: 0.5pt solid windowtext; }.xl65 { border: 0.5pt solid windowtext; }</style> <table style="border-collapse: collapse; width: 187px; height: 573px;" border="0" cellpadding="0" cellspacing="0"> <col style="width: 65pt;" width="65"> <col style="width: 65pt;" span="3" width="65"> <tbody><tr style="height: 15pt;" height="15"> <td class="xl63" style="height: 15pt; width: 65pt;" width="65" height="15">Number</td> <td class="xl63" style="border-left: medium none; width: 65pt;" width="65">Name</td> <td class="xl63" style="border-left: medium none; width: 65pt;" width="65">Qualification</td> <td class="xl63" style="border-left: medium none; width: 65pt;" width="65">Date</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl64" style="height: 15pt; border-top: medium none;" align="right" height="15">43132</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">Dave</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">Matt3a</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">13/05/2011</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl64" style="height: 15pt; border-top: medium none;" align="right" height="15">657675</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">French</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">Matt3a</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">01/12/1990</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl64" style="height: 15pt; border-top: medium none;" align="right" height="15">3241432</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">James</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">Matt3a</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">01/12/1990</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl64" style="height: 15pt; border-top: medium none;" align="right" height="15">123434</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">John</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">Matt3a</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">13/05/2011</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl64" style="height: 15pt; border-top: medium none;" align="right" height="15">324134</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">Kyle</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">Matt3a</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">03/05/2010</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl64" style="height: 15pt; border-top: medium none;" align="right" height="15">675765765</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">Milly</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">Matt3a</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">18/09/2007</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl64" style="height: 15pt; border-top: medium none;" align="right" height="15">43132</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">Dave</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">Matt3b</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">01/12/1990</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl64" style="height: 15pt; border-top: medium none;" align="right" height="15">657675</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">French</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">Matt3b</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">07/07/2011</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl64" style="height: 15pt; border-top: medium none;" align="right" height="15">3241432</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">James</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">Matt3b</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">13/05/2011</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl64" style="height: 15pt; border-top: medium none;" align="right" height="15">123434</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">John</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">Matt3b</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">07/07/2011</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl64" style="height: 15pt; border-top: medium none;" align="right" height="15">324134</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">Kyle</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">Matt3b</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">13/05/2011</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl64" style="height: 15pt; border-top: medium none;" align="right" height="15">675765765</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">Milly</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">Matt3b</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">07/07/2011</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl64" style="height: 15pt; border-top: medium none;" align="right" height="15">43132</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">Dave</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">Matt4b</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">07/07/2011</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl64" style="height: 15pt; border-top: medium none;" align="right" height="15">657675</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">French</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">Matt4b</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">07/07/2011</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl64" style="height: 15pt; border-top: medium none;" align="right" height="15">3241432</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">James</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">Matt4b</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">07/07/2011</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl64" style="height: 15pt; border-top: medium none;" align="right" height="15">123434</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">John</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">Matt4b</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">18/09/2007</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl64" style="height: 15pt; border-top: medium none;" align="right" height="15">324134</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">Kyle</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">Matt4b</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">13/05/2011</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl64" style="height: 15pt; border-top: medium none;" align="right" height="15">675765765</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">Milly</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">Matt4b</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">07/07/2011</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl64" style="height: 15pt; border-top: medium none;" align="right" height="15">43132</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">Dave</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">Matt4c</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">13/05/2011</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl64" style="height: 15pt; border-top: medium none;" align="right" height="15">657675</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">French</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">Matt4c</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">01/12/1990</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl64" style="height: 15pt; border-top: medium none;" align="right" height="15">3241432</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">James</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">Matt4c</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">01/12/1990</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl64" style="height: 15pt; border-top: medium none;" align="right" height="15">123434</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">John</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">Matt4c</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">13/05/2011</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl64" style="height: 15pt; border-top: medium none;" align="right" height="15">324134</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">Kyle</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">Matt4c</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">07/07/2011</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl64" style="height: 15pt; border-top: medium none;" align="right" height="15">675765765</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">Milly</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">Matt4c</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">13/05/2011</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl64" style="height: 15pt; border-top: medium none;" align="right" height="15">675765765</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">Milly</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">Matt5</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">07/07/2011</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl64" style="height: 15pt; border-top: medium none;" align="right" height="15">657675</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">French</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">Matt5</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">18/09/2007</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl64" style="height: 15pt; border-top: medium none;" align="right" height="15">43132</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">Dave</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">Matt5</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">18/09/2007</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl64" style="height: 15pt; border-top: medium none;" align="right" height="15">123434</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">John</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">MAtt5</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">13/05/2011</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl64" style="height: 15pt; border-top: medium none;" align="right" height="15">324134</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">Kyle</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">Matt5</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">07/07/2011</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl64" style="height: 15pt; border-top: medium none;" align="right" height="15">3241432</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">James</td> <td class="xl64" style="border-top: medium none; border-left: medium none;">Matt5</td> <td class="xl65" style="border-top: medium none; border-left: medium none;" align="right">03/05/2010</td> </tr> </tbody></table>

This is where the dates need to go to: Sheet B
<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl63 { border: 0.5pt solid windowtext; }.xl64 { font-weight: 700; vertical-align: middle; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(184, 204, 228); }.xl65 { font-weight: 700; vertical-align: middle; border: 0.5pt solid windowtext; background: none repeat scroll 0% 0% rgb(184, 204, 228); white-space: normal; }</style> <table style="border-collapse: collapse; width: 321px; height: 115px;" border="0" cellpadding="0" cellspacing="0"> <col style="width: 65pt;" span="2" width="65"> <col style="width: 43pt;" width="43"> <col style="width: 44pt;" span="2" width="44"> <col style="width: 43pt;" width="43"> <col style="width: 42pt;" width="42"> <tbody><tr style="height: 30pt;" height="30"> <td class="xl64" style="height: 30pt; width: 65pt;" width="65" height="30">Number</td> <td class="xl64" style="border-left: medium none; width: 65pt;" width="65">Name</td> <td class="xl65" style="border-left: medium none; width: 43pt;" width="43">Matt3a Date</td> <td class="xl65" style="border-left: medium none; width: 44pt;" width="44">Matt3b Date</td> <td class="xl65" style="border-left: medium none; width: 44pt;" width="44">Matt4b Date</td> <td class="xl65" style="border-left: medium none; width: 43pt;" width="43">Matt4c Date</td> <td class="xl65" style="border-left: medium none; width: 42pt;" width="42">Matt5 Date</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl63" style="height: 15pt; border-top: medium none;" align="right" height="15">43132</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">Dave</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl63" style="height: 15pt; border-top: medium none;" align="right" height="15">657675</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">French</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl63" style="height: 15pt; border-top: medium none;" align="right" height="15">3241432</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">James</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl63" style="height: 15pt; border-top: medium none;" align="right" height="15">123434</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">John</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl63" style="height: 15pt; border-top: medium none;" align="right" height="15">324134</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">Kyle</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">
</td> </tr> <tr style="height: 15pt;" height="15"> <td class="xl63" style="height: 15pt; border-top: medium none;" align="right" height="15">675765765</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">Milly</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">
</td> <td class="xl63" style="border-top: medium none; border-left: medium none;">
</td> </tr> </tbody></table>
Now, I suppose I can filter the data by qualification and then copy Sheet A into the same workbook as Sheet B each time and do a vlookup that way, but with all the different employees and different qualifications, I’d prefer to work out a formula to simplify and save me some time and be able to work it all out in one go.

Hope this makes sense?!

Thanks
<style>@font-face { font-family: "ＭＳ 明朝"; }@font-face { font-family: "Cambria Math"; }@font-face { font-family: "Cambria"; }p.MsoNormal, li.MsoNormal, div.MsoNormal { margin: 0cm 0cm 0.0001pt; font-size: 12pt; font-family: Cambria; }.MsoChpDefault { font-family: Cambria; }div.WordSection1 { page: WordSection1; }div.WordSection2 { page: WordSection2; }</style>

 

[njimack]

Excel Workbook
	A	B	C	D	E	F	G
1	Number	Name	Matt3a	Matt3b	Matt4b	Matt4c	Matt5
2	43132	Dave	13/05/11	01/12/90	07/07/11	13/05/11	00/01/00
3	657675	French	01/12/90	07/07/11	07/07/11	01/12/90	00/01/00
4	3241432	James	01/12/90	13/05/11	07/07/11	01/12/90	00/01/00
5	123434	John	13/05/11	07/07/11	18/09/07	13/05/11	00/01/00
6	324134	Kyle	03/05/10	13/05/11	13/05/11	07/07/11	00/01/00
7	6.76E+08	Milly	18/09/07	07/07/11	07/07/11	13/05/11	00/01/00
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2	1. / Cell Value equal to =0	Abc
D2	1. / Cell Value equal to =0	Abc
E2	1. / Cell Value equal to =0	Abc
F2	1. / Cell Value equal to =0	Abc
G2	1. / Cell Value equal to =0	Abc
C3	1. / Cell Value equal to =0	Abc
D3	1. / Cell Value equal to =0	Abc
E3	1. / Cell Value equal to =0	Abc
F3	1. / Cell Value equal to =0	Abc
G3	1. / Cell Value equal to =0	Abc
C4	1. / Cell Value equal to =0	Abc
D4	1. / Cell Value equal to =0	Abc
E4	1. / Cell Value equal to =0	Abc
F4	1. / Cell Value equal to =0	Abc
G4	1. / Cell Value equal to =0	Abc
C5	1. / Cell Value equal to =0	Abc
D5	1. / Cell Value equal to =0	Abc
E5	1. / Cell Value equal to =0	Abc
F5	1. / Cell Value equal to =0	Abc
G5	1. / Cell Value equal to =0	Abc
C6	1. / Cell Value equal to =0	Abc
D6	1. / Cell Value equal to =0	Abc
E6	1. / Cell Value equal to =0	Abc
F6	1. / Cell Value equal to =0	Abc
G6	1. / Cell Value equal to =0	Abc
C7	1. / Cell Value equal to =0	Abc
D7	1. / Cell Value equal to =0	Abc
E7	1. / Cell Value equal to =0	Abc
F7	1. / Cell Value equal to =0	Abc
G7	1. / Cell Value equal to =0	Abc

 

[johannsutherland]

Thanks Neil!That's great!But what if the qualification name is longer that just matt3a? something like aag/matt3b-jksl/1 or aag/matt3a-jksl/2. And the column that you enter the date into hasn't got the exact same name as the actual qualification?

 

[Haseeb Avarakkan]

Try,

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;">Number</td><td style="font-weight: bold;text-align: center;;">Name</td><td style="font-weight: bold;text-align: center;;">Matt3a</td><td style="font-weight: bold;text-align: center;;">Matt3b</td><td style="font-weight: bold;text-align: center;;">Matt4b</td><td style="font-weight: bold;text-align: center;;">Matt4c</td><td style="font-weight: bold;text-align: center;;">Matt5</td><td style="font-weight: bold;text-align: center;;">Matt5</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">43132</td><td style=";">Dave</td><td style="text-align: right;;">13-May-11</td><td style="text-align: right;;">12-Jan-90</td><td style="text-align: right;;">7-Jul-11</td><td style="text-align: right;;">13-May-11</td><td style="text-align: right;;">18-Sep-07</td><td style="text-align: right;;">18-Sep-07</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">657675</td><td style=";">French</td><td style="text-align: right;;">12-Jan-90</td><td style="text-align: right;;">7-Jul-11</td><td style="text-align: right;;">7-Jul-11</td><td style="text-align: right;;">12-Jan-90</td><td style="text-align: right;;">18-Sep-07</td><td style="text-align: right;;">18-Sep-07</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">3241432</td><td style=";">James</td><td style="text-align: right;;">12-Jan-90</td><td style="text-align: right;;">13-May-11</td><td style="text-align: right;;">7-Jul-11</td><td style="text-align: right;;">12-Jan-90</td><td style="text-align: right;;">5-Mar-10</td><td style="text-align: right;;">5-Mar-10</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">123434</td><td style=";">John</td><td style="text-align: right;;">13-May-11</td><td style="text-align: right;;">7-Jul-11</td><td style="text-align: right;;">18-Sep-07</td><td style="text-align: right;;">13-May-11</td><td style="text-align: right;;">13-May-11</td><td style="text-align: right;;">13-May-11</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">324134</td><td style=";">Kyle</td><td style="text-align: right;;">5-Mar-10</td><td style="text-align: right;;">13-May-11</td><td style="text-align: right;;">13-May-11</td><td style="text-align: right;;">7-Jul-11</td><td style="text-align: right;;">7-Jul-11</td><td style="text-align: right;;">7-Jul-11</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">675765765</td><td style=";">Milly</td><td style="text-align: right;;">18-Sep-07</td><td style="text-align: right;;">7-Jul-11</td><td style="text-align: right;;">7-Jul-11</td><td style="text-align: right;;">13-May-11</td><td style="text-align: right;;">7-Jul-11</td><td style="text-align: right;;">7-Jul-11</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">{=IFERROR(<font color="Blue">INDEX(<font color="Red">Sheet1!$D$2:$D$31,MATCH(<font color="Green">1,IF(<font color="Purple">Sheet1!$A$2:$A$31=$A2,IF(<font color="Teal">ISNUMBER(<font color="#FF00FF">SEARCH(<font color="Navy">C$1,Sheet1!$C$2:$C$31</font>)</font>),1</font>)</font>),0</font>)</font>),""</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
Copy down & across...

 

[johannsutherland]

Haseeb, the formula just gives me a blank and when I copy it down, it says the formula refers to a blank cell...strange

 

[njimack]

Thanks Neil!That's great!But what if the qualification name is longer that just matt3a? something like aag/matt3b-jksl/1 or aag/matt3a-jksl/2. And the column that you enter the date into hasn't got the exact same name as the actual qualification?

=SUMPRODUCT(--(Sheet1!$A$2:$A$31=$A2),--(ISNUMBER(SEARCH(C$1,Sheet1!$C$2:$C$31))),(Sheet1!$D$2:$D$31))