Vlookup date by employee number and qualification at the same time

johannsutherland

New Member
Joined
Mar 8, 2011
Messages
3
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: "MS 明朝"; }@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>
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C21. / Cell Value equal to =0Abc
D21. / Cell Value equal to =0Abc
E21. / Cell Value equal to =0Abc
F21. / Cell Value equal to =0Abc
G21. / Cell Value equal to =0Abc
C31. / Cell Value equal to =0Abc
D31. / Cell Value equal to =0Abc
E31. / Cell Value equal to =0Abc
F31. / Cell Value equal to =0Abc
G31. / Cell Value equal to =0Abc
C41. / Cell Value equal to =0Abc
D41. / Cell Value equal to =0Abc
E41. / Cell Value equal to =0Abc
F41. / Cell Value equal to =0Abc
G41. / Cell Value equal to =0Abc
C51. / Cell Value equal to =0Abc
D51. / Cell Value equal to =0Abc
E51. / Cell Value equal to =0Abc
F51. / Cell Value equal to =0Abc
G51. / Cell Value equal to =0Abc
C61. / Cell Value equal to =0Abc
D61. / Cell Value equal to =0Abc
E61. / Cell Value equal to =0Abc
F61. / Cell Value equal to =0Abc
G61. / Cell Value equal to =0Abc
C71. / Cell Value equal to =0Abc
D71. / Cell Value equal to =0Abc
E71. / Cell Value equal to =0Abc
F71. / Cell Value equal to =0Abc
G71. / Cell Value equal to =0Abc
 

johannsutherland

New Member
Joined
Mar 8, 2011
Messages
3
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

Well-known Member
Joined
Sep 28, 2010
Messages
902
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Mar 8, 2011
Messages
3
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

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
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?

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

Forum statistics

Threads
1,141,017
Messages
5,703,752
Members
421,313
Latest member
Mooncake1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top