Help creating a new column based on column queries

Im trying to create a new column (Opposition) based on a query that looks at the Team column and the Home & Away team and adds the Opposition based on what the Team column lists.