Excel Formula

[maslam15]

Hello,
I need help with one Excel formula.
I have many rows and columns on my table and the name of the table is Table1

I have one column which contains options such as Development and Production
I have one column called ID which can be and not be in both Production and Development.
What I want to find on a separate column is is that If an ID exists in Production but not in Development, it should say Yes or else it should say No

Thank you for your help I appreciate it,

 

[Special-K99]

So you have two columns.
Column A contains the values of Development or Production, no other values.
Column B is an ID.

If an ID exists with a Production entry in column A but no Development entry for that ID in column A the result should be Yes otherwise No.

That implies if an ID exists for both Production and Development then the result is No.
And if an ID exists for Development only (no Production) entry the result will also be No.


I'm assuming data is in column A (Options) and B (ID) and starts at row 2, adjust the formula to fit if this is incorrect as you haven't mentioned any start rows or columns references.

=IF(AND(A2="Production",ISNA(LOOKUP(2,1/((B$2:B$1000=B2)*(A$2:A$1000=IF(A2="Development","Production","Development"))),(A$2:A$1000)))),"Yes","No")

 

[DanteAmor]

I am trying to guess how you have your information. If it is not something similar to the following, then I suggest you provide an example of what you have and what you expect from the result

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:90.3px;" /><col style="width:164.44px;" /><col style="width:39.92px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >ID</td><td >OPTION</td><td > </td><td style="background-color:#ffff00; ">ID</td><td style="background-color:#ffff00; ">Result</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >A102</td><td >Production</td><td > </td><td >A102</td><td >Yes</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >A102</td><td >Production</td><td > </td><td >A103</td><td >No</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >A102</td><td >Production</td><td > </td><td >A104</td><td >No</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >A102</td><td >Production</td><td > </td><td >A105</td><td >Yes</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >A103</td><td >Production</td><td > </td><td >A106</td><td >No</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >A103</td><td >Development</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >A103</td><td >Development</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >A103</td><td >Production</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >A104</td><td >Development</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >A104</td><td >Development</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td >A104</td><td >Development</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td >A104</td><td >Development</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td >A105</td><td >Production</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td >A105</td><td >Production</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td >A105</td><td >Production</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td >A106</td><td >Development</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td >A106</td><td >Production</td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >E2</td><td >=IF(COUNTIF(Tabla1[ID],D2)-COUNTIFS(Tabla1[ID],D2,Tabla1[OPTION],"Production")=0,"Yes","No")</td></tr></table></td></tr></table>