lookup values in range and return yes if all values are found.

[droot]

Hi, I'm stuck with a problem and can't work out the solution.

I'm trying to lookup IDs and return Yes or No depending on if all of the values correspond with the ID.

This is an example of my table -

ID​	Product​
1​	Red​
1​	Blue​
1​	Green​
1​	Yellow​
2​	Red​
2​	Blue​
3​	Red​
3​	Blue​
3​	Green​
3​	Yellow​

These are the values -

Values​

Red​

Blue​

Green​

Yellow​

So ID "1" will return Yes, ID "2" will return No and ID "3" will return Yes.

Hope this makes sense.
Thanks in advance.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have Microsoft 365, you could try this. I have assumed that the only possible colours are the four that you have mentioned.

21 08 02.xlsm
	A	B	C	D	E
1	ID	Product
2	1	Red		1	Yes
3	1	Blue		2	No
4	1	Green		3	Yes
5	1	Yellow
6	2	Red
7	2	Blue
8	3	Red
9	3	Blue
10	3	Green
11	3	Yellow
Check All

Cell Formulas
Range	Formula
D2:D4	D2	=UNIQUE(A2:A11)
E2:E6	E2	=IF(D2="","",IF(ROWS(UNIQUE(FILTER(B$2:B$11,A$2:A$11=D2)))=4,"Yes","No"))
Dynamic array formulas.

 

[droot]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you have Microsoft 365, you could try this. I have assumed that the only possible colours are the four that you have mentioned.

21 08 02.xlsm
	A	B	C	D	E
1	ID	Product
2	1	Red		1	Yes
3	1	Blue		2	No
4	1	Green		3	Yes
5	1	Yellow
6	2	Red
7	2	Blue
8	3	Red
9	3	Blue
10	3	Green
11	3	Yellow
Check All

Cell Formulas
Range	Formula
D2:D4	D2	=UNIQUE(A2:A11)
E2:E6	E2	=IF(D2="","",IF(ROWS(UNIQUE(FILTER(B$2:B$11,A$2:A$11=D2)))=4,"Yes","No"))
Dynamic array formulas.

Thanks for your reply.

I haven't explained this very well, let me try again.

I have two sheets. Sheet1 contains the raw data and Sheet2 contains just the unique IDs. In Sheet2 I'm trying to use a lookup formula to check the data in Sheet 1 and trying to return Yes if all of the key values are present.

This is what I've tried but doesn't work -
=IF(VLOOKUP(A2,Sheet1!$A$2:$B$10,2,FALSE)={"Red","Blue","Green","Yellow"},"Yes","No")

I think the vlookup is only checking the first instance and looking for any of the strings in the curly brackets instead of all.

 

[Peter_SSs]

Thanks for updating your profile.

In what way does the FILTER formula I suggested, adapted for two sheets, fail?

droot.xlsm
	A	B
1	ID	Product
2	1	Red
3	1	Blue
4	1	Green
5	1	Yellow
6	2	Red
7	2	Blue
8	3	Red
9	3	Blue
10	3	Green
11	3	Yellow
12
Sheet1

droot.xlsm
	A	B
1	Unique IDs	Check
2	1	Yes
3	2	No
4	3	Yes
5
6
Sheet2

Cell Formulas
Range	Formula
B2:B6	B2	=IF(A2="","",IF(ROWS(UNIQUE(FILTER(Sheet1!B$2:B$11,Sheet1!A$2:A$11=A2)))=4,"Yes","No"))

 

[droot]

Thanks for updating your profile.

In what way does the FILTER formula I suggested, adapted for two sheets, fail?

droot.xlsm
	A	B
1	ID	Product
2	1	Red
3	1	Blue
4	1	Green
5	1	Yellow
6	2	Red
7	2	Blue
8	3	Red
9	3	Blue
10	3	Green
11	3	Yellow
12
Sheet1

droot.xlsm
	A	B
1	Unique IDs	Check
2	1	Yes
3	2	No
4	3	Yes
5
6
Sheet2

Cell Formulas
Range	Formula
B2:B6	B2	=IF(A2="","",IF(ROWS(UNIQUE(FILTER(Sheet1!B$2:B$11,Sheet1!A$2:A$11=A2)))=4,"Yes","No"))

I can have an unlimited number of IDs with various different values but I'm only interested in a particular set. That formula seems to be looking for 4 uniques before returning a Yes (if I'm interpreting it correctly).

 

[Fluff]

Maybe

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1	ID	Product
2	1	Red		1	Yes		Red
3	1	Blue		2	No		Blue
4	1	Green		3	Yes		Green
5	1	Yellow					Yellow
6	2	Red
7	2	Blue
8	3	Red
9	3	Blue
10	3	Green
11	3	Yellow
12
List

Cell Formulas
Range	Formula
E2:E4	E2	=IF(SUM(--IFERROR($G$2:$G$5=FILTER($B$2:$B$11,$A$2:$A$11=D2),0))=4,"Yes","No")

 

[Peter_SSs]

I can have an unlimited number of IDs with various different values but I'm only interested in a particular set.

OK, see if this is better. I have introduced some more colours.

droot.xlsm
	A	B
1	ID	Product
2	1	Red
3	1	Blue
4	1	Green
5	1	Yellow
6	2	Red
7	2	Blue
8	3	Red
9	3	Blue
10	3	Green
11	3	Pink
12	4	Red
13	4	Yellow
14	4	Orange
15	4	Green
16	4	Blue
17	4	Green
18
19
20
Sheet1

Check 1 uses the list in column F
If you don't want to use a list in the worksheet but have the LET function then try Check 2
If you also do not have the LET function then try Check 3

droot.xlsm
	A	B	C	D	E	F
1	Unique IDs	Check 1	Check 2	Check 3
2	1	Yes	Yes	Yes		Red
3	2	No	No	No		Blue
4	3	No	No	No		Green
5	4	Yes	Yes	Yes		Yellow
Sheet2

Cell Formulas
Range	Formula
B2:B5	B2	=IF(COUNT(MATCH(F$2:F$5,FILTER(Sheet1!B$2:B$20,Sheet1!A$2:A$20=A2),0))=ROWS(F$2:F$5),"Yes","No")
C2:C5	C2	=LET(clrs,{"Red","Blue","Green","Yellow"},IF(COUNT(MATCH(clrs,FILTER(Sheet1!B$2:B$20,Sheet1!A$2:A$20=A2),0))=COUNTA(clrs),"Yes","No"))
D2:D5	D2	=IF(COUNT(MATCH({"Red","Blue","Green","Yellow"},FILTER(Sheet1!B$2:B$20,Sheet1!A$2:A$20=A2),0))=COUNTA({"Red","Blue","Green","Yellow"}),"Yes","No")