I have some product IDs. I want to be able to return the Sales status if there's an active opportunity on that product, regardless of the date of the opportunity.

Table 1
Product ID - Opportunity Status
1234 - Active
5678 - Active
9101 - Closed
9102 - Closed
9102 - Active
1234 - Active

Table 2
Product ID - Opportunity Status
1234 - ?
5678 - ?
9101 - ?
9102 - ?

As ou can see, products 1234 and 9102 appear twice. One opportunity is closed, the other is active. I want my Table 2, to return "Active" for both 1234 and 9102 since they have active opportunities in Table 1. How do I do this?

  • Do you have a column for the date? Commented Aug 24, 2018 at 19:30
  • With that precedent, you can ignore "Closed" completely and only test for active "Active". There are a number of ways to do this. Personally, I would use sumproduct. Commented Aug 25, 2018 at 3:06

1 Answer 1


Since I had a pretty complex way of handling my "hierarchy" of opportunities, I decided to assign scores to each of my opportunities based on its type and status, and then used the following:


Where H29:H37 are order ID's in table 2, which would be compared to the order ID (M28) in Table 1, I29:I3 was the score, and J29:J37 was the Opportunity Status. I hope this helps others!

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .