Excel: Get a particular value by key from a set of rows

Question

I have an Excel table with purchase orders. Eg:

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

I need to add an automatic column (obtained with a formula) with the status of the order:

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

Explanation:

The order 1 has one line received and one line waiting, so the formula must return waiting, which is the status of the whole order. When the line 2 is received, both lines would be in received status, so the order status calculated by the formula must be received

Answer 1

Use COUNTIFS in an IF:

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

Put in D2 and copy down.