Update fields based on common row names but with a lookedup logic

[rajeshi1980]

Iam looking to find a fomula for the status column. I have product family in first column, and its quantity in second column, in third column i need to display if the product family is available as a set or it is imcomplete set, based on quantity availibility. below table i have tried to explain the output that is required.


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]product family[/TD]
[TD]qty[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]abcd[/TD]
[TD]3[/TD]
[TD]Available as a set[/TD]
[/TR]
[TR]
[TD]abcd[/TD]
[TD]7[/TD]
[TD]Available as a set[/TD]
[/TR]
[TR]
[TD]pqr[/TD]
[TD]1[/TD]
[TD]Incomplete set[/TD]
[/TR]
[TR]
[TD]pqr[/TD]
[TD]9[/TD]
[TD]Incomplete set[/TD]
[/TR]
[TR]
[TD]pqr[/TD]
[TD]0[/TD]
[TD]Incomplete set[/TD]
[/TR]
[TR]
[TD]xyz[/TD]
[TD]4[/TD]
[TD]Available as a set[/TD]
[/TR]
[TR]
[TD]xyz[/TD]
[TD]3[/TD]
[TD]Available as a set[/TD]
[/TR]
[TR]
[TD]xyz[/TD]
[TD]3[/TD]
[TD]Available as a set[/TD]
[/TR]
[TR]
[TD]kite[/TD]
[TD]0[/TD]
[TD]Not Available[/TD]
[/TR]
[TR]
[TD]kite[/TD]
[TD]0[/TD]
[TD]Not Available[/TD]
[/TR]
</tbody>[/TABLE]


Pls help,

 

[rajeshi1980]

got it...

{=IF(MIN(IF($A$2:$A$13=A2,$B$2:$B$13))>0,"Set Available","Set Not Available")}

thanks...