If function for multiple occurrences and one criteria

[miraj42]

Hi Everyone,

I've been trying to get a vlookup formula to work but just realised that vlookup only looks up the first occurrence of a match. How do I make an if function
which returns a value if ANY occurrence of a match meets the criteria?

i.e.

My raw data looks like this:

Site ID	Name	Received Badge
101	Jim	0
101	Bill	1
103	Rose	0
105	Selena	1
103	Chris	0

I want to create a function which looks up the Site ID and if any one of the people at the site have received a badge (=1) then to return 100% as the output value, if not then 0%.

I appreciate any help on this one or pointers to other useful pages.

Thanks

miraj42

 

[Fluff]

How about

+Fluff.xlsm
	A	B	C	D
1	Site ID	Name	Received Badge
2	101	Jim	0	100%
3	101	Bill	1	100%
4	103	Rose	0	0%
5	105	Selena	1	100%
6	103	Chris	0	0%
Data

Cell Formulas
Range	Formula
D2:D6	D2	=--(COUNTIFS(A:A,A2,C:C,1)>0)

 

[miraj42]

How about

+Fluff.xlsm
	A	B	C	D
1	Site ID	Name	Received Badge
2	101	Jim	0	100%
3	101	Bill	1	100%
4	103	Rose	0	0%
5	105	Selena	1	100%
6	103	Chris	0	0%
Data

Cell Formulas
Range	Formula
D2:D6	D2	=--(COUNTIFS(A:A,A2,C:C,1)>0)

Hi @Fluff thank you! What a quick and simple solution!

 

[Fluff]

You're welcome & thanks for the feedback