Excel：通过一组行中的键获取特定值

Question

我有一个带有采购订单的Excel表。例如：

order | order line | line status 
----------------------------------- 
1  | 1   | Received  
1  | 2   | Waiting 
2  | 1   | Received  

我需要与订单的状态添加的自动柱（用公式获得）：

order | order line | line status | order status 
------------------------------------------------------- 
1  | 1   | Received  | Waiting 
1  | 2   | Waiting  | Waiting 
2  | 1   | Received  | Received 

说明：

顺序具有一个线收到和一行等待，所以公式必须返回等待，wh ich是整个订单的状态。当接收到线路，这两条线将是接收状态，所以由以下公式计算的顺序的状态必须为接收

Answer 1

使用COUNTIFS在IF：

=IF(COUNTIFS(A:A,A2,C:C,"Waiting"),"Waiting","Received") 

穿戴在D2中并抄下来。