How to search through items with the same value to see if the meet criteria?

Hi,

I’m trying to search through multiple items (of which there are duplicates) to see if they have ever returned a TRUE result. The problem I’m having is that the search stops at the first match, so, if this is a FALSE, the answer is returned as FALSE. Even if the next match would return a TRUE result. 

I’ve created a scenario below to try and illustrate the problem (link goes to a Flickr image of the example Excel table): 

https://flic.kr/p/2cZAmP7

In column A I’d like to return a Y/N answer to whether the ‘Animal Type’ adjacent, in column B, has ever won the olympics.

I’ve tried using the ‘if’ and ‘Index Match’ functions, but the formula stops at the first match it finds in the column. 

For example, the dogs in row 2 and 5 have not won the olympics, but the dog in row 6 has; the actual answer to the question of whether a dog has ever won the olympics is ‘Y’ (yes), but with the ‘IF’ and ‘Index/match’ functions the answer returned is ‘N’ (No)

Does anyone have a formula that would be able to search through all the instances of ‘Dog’ in Column B to see if there is any instance where ‘Dog’ has ‘Won the Olympics’ (C=”Y”).

Could this formula then be translated to other cells, so, in row 3, a Y/N answer will be returned for whether ‘man’ has won the Olympics?

Any assistance would be gratefully received. Also, if the above is unclear, I’m happy to try and explain further.

Many thanks!

By: Beth

Leave a Reply

Your email address will not be published. Required fields are marked *