Creating a formula to check if combination of codes is available

[johanexcel]

Dear all,

I would like to request some help with creating a formula.

I have 1 data sheet with account numbers (column A) and departments (Column B). Not all account numbers are available with all departments.

Account	Department
4000	15
4000	16
4000	21
4001	15
4001	17
4001	20

<tbody>
</tbody>

In the input sheet (which will be completed manually) I would like to have a control to see if the combination of account number and department is a valid combination. What formula could I use?

Many thanks in advance for your expertise!

Johan

 

[Oeldere]

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;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></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="border-top: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">Lookup table</td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="background-color: #B4C6E7;;">INPUT DATA</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Account</td><td style="text-align: right;color: #574123;background-color: #FFFFFF;;">Department</td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;color: #574123;background-color: #FFFFFF;;">Account</td><td style="text-align: right;color: #574123;background-color: #FFFFFF;;">Department</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">4000</td><td style="text-align: right;color: #574123;background-color: #FFFFFF;;">15</td><td style="text-align: right;border-right: 1px solid black;;">400015</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;color: #574123;background-color: #FFFFFF;;">4000</td><td style="text-align: right;color: #574123;background-color: #FFFFFF;;">421</td><td style="background-color: #92D050;;">data not valid</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">4000</td><td style="text-align: right;color: #574123;background-color: #FFFFFF;;">16</td><td style="text-align: right;border-right: 1px solid black;;">400016</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;color: #574123;background-color: #FFFFFF;;">4000</td><td style="text-align: right;color: #574123;background-color: #FFFFFF;;">16</td><td style="background-color: #92D050;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">4000</td><td style="text-align: right;color: #574123;background-color: #FFFFFF;;">21</td><td style="text-align: right;border-right: 1px solid black;;">400021</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;color: #574123;background-color: #FFFFFF;;">4000</td><td style="text-align: right;color: #574123;background-color: #FFFFFF;;">21</td><td style="background-color: #92D050;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">4001</td><td style="text-align: right;color: #574123;background-color: #FFFFFF;;">15</td><td style="text-align: right;border-right: 1px solid black;;">400115</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;color: #574123;background-color: #FFFFFF;;">4001</td><td style="text-align: right;color: #574123;background-color: #FFFFFF;;">15</td><td style="background-color: #92D050;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">4001</td><td style="text-align: right;color: #574123;background-color: #FFFFFF;;">17</td><td style="text-align: right;border-right: 1px solid black;;">400117</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;color: #574123;background-color: #FFFFFF;;">4001</td><td style="text-align: right;color: #574123;background-color: #FFFFFF;;">17</td><td style="background-color: #92D050;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">4001</td><td style="text-align: right;border-bottom: 1px solid black;color: #574123;background-color: #FFFFFF;;">20</td><td style="text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;;">400120</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;color: #574123;background-color: #FFFFFF;;">4001</td><td style="text-align: right;color: #574123;background-color: #FFFFFF;;">20</td><td style="background-color: #92D050;;"></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 #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Blad12</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>Worksheet 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: #BBB"><thead><tr style=" background-color: #DAE7F5;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: #DAE7F5;color: #161120">C3</th><td style="text-align:left">=A3&B3</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C4</th><td style="text-align:left">=A4&B4</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C5</th><td style="text-align:left">=A5&B5</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C6</th><td style="text-align:left">=A6&B6</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C7</th><td style="text-align:left">=A7&B7</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">C8</th><td style="text-align:left">=A8&B8</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G3</th><td style="text-align:left">=IFERROR(<font color="Blue">IF(<font color="Red">MATCH(<font color="Green">E3&F3,C3:C8,0</font>)>0,"",</font>),"data not valid"</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G4</th><td style="text-align:left">=IFERROR(<font color="Blue">IF(<font color="Red">MATCH(<font color="Green">E4&F4,C4:C9,0</font>)>0,"",</font>),"data not valid"</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G5</th><td style="text-align:left">=IFERROR(<font color="Blue">IF(<font color="Red">MATCH(<font color="Green">E5&F5,C5:C10,0</font>)>0,"",</font>),"data not valid"</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G6</th><td style="text-align:left">=IFERROR(<font color="Blue">IF(<font color="Red">MATCH(<font color="Green">E6&F6,C6:C11,0</font>)>0,"",</font>),"data not valid"</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G7</th><td style="text-align:left">=IFERROR(<font color="Blue">IF(<font color="Red">MATCH(<font color="Green">E7&F7,C7:C12,0</font>)>0,"",</font>),"data not valid"</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G8</th><td style="text-align:left">=IFERROR(<font color="Blue">IF(<font color="Red">MATCH(<font color="Green">E8&F8,C8:C13,0</font>)>0,"",</font>),"data not valid"</font>)</td></tr></tbody></table></td></tr></table><br />