XLookup and IF formula

[Aewilliams]

I have two worksheets and am trying to use a formula to populate a column looking at information on both. Any help would be much appreciated.....

Tab 1
Column A contains a Ref and Column B contains a tag in the form of a colour, either Black or Red
Each reference can be tagged as black, red or black and red (for black and red there would be more than one row for the reference e.g. a row showing it tagged as black and another row showing it tagged as red e.g.)

View attachment 118498

Tab 2
I want to add a formula to columns B, C and D to look at the ref in tab 2, find that ref in tab 1 and if the ref is tagged as Red then it will return a Yes in column B. If that ref also appears on Tab 1 with a Black tag, I want a Yes to show in column C. I tried an XLookup and a VLookup but can't get either to work.

 

[dreid1011]

What about col D?

Wit the following data, what results do you expect to see on tab 2?

Book1
	A	B
1	Ref	Tag
2	1	Black
3	3	Red
4	2	Red
5	4	Red
6	4	Black
7	2	Black
Sheet1

 

[RoryA]

Perhaps something like:

=IF(COUNTIFS(Tab1!$A:$A,$A2,Tab1!$B:$B,B$1),"Yes","No")

in B2, then copy across to C2, and fill both down.

Then in D2:

=IF(AND(B2="Yes",C2="Yes"),"Yes","No")

Unless you want only one Yes per row.