Formula to find an item on another sheet, then reference its "completion"

[barryweston]

Sorry if the title is not the most descriptive!

I am trying to find a way to do the following.

- I have a list of product codes/SKUs on a sheet. on another sheet, I have a smaller list of SKUs.
- I need to come up with a way for the main larger sheet to look into the smaller list, find the matching SKU and then look to see if the columns associated with that SKU are completed
- There are three to four columns for each SKU on the secondary page. When they are all marked as Yes for complete, I want the SKU on the main page to say Yes for an overall completion or No if all columns are not satisfied as Yes.

Sorry if that does not make sense, hoping someone can help!

I assumed OFFSET would be helpful, so I was able to use that OFFSET and MATCH to find the SKU on the second page, but when I add in further logic to look to see if all three columns = yes, the formula breaks.

[barryweston]

Snipaste_2018-08-16_11-17-18.pngSnipaste_2018-08-16_11-17-31.png

[Sam Capricci]

this would be one way...
=IF(COUNTIFS(Sheet2!A:A,A2,Sheet2!c:c,"yes",Sheet2!d:d,"yes",Sheet2!e:e,"yes")=1,"yes","no") though I don't know your tab names.

[barryweston]

The sheet names are Worksheet and Molex SKUs

The list on Molex SKUs only has about 390 SKUs where the Worksheet has 7000+. So I need it to find if it exists on that sheet first. I think I have have used a similar formula to that and it didn't work but I will try it.

Thanks!

[barryweston]

To be honest, I'm not sure how to use that formula you recommended. My work with excel is pretty limited

[Sam Capricci]

it is difficult to create a formula for a picture. If you post a sample sheet with desensitized data and your expected results that will help get you answers specific to your needs.
However...
=COUNTIFS(Sheet2!A:A,A2,Sheet2!c:c,"yes",Sheet2!d:d,"yes",Sheet2!e:e,"yes")=1
Bold red part means that in the sheet pictured at the top of post #2 the formula is looking to count the SKU (sheet2!A:A) and the A2 is referring to your picture at the bottom of post #2.
this portion (bold red) =IF(COUNTIFS(Sheet2!A:A,A2,Sheet2!c:c,"yes",Sheet2!d:d,"yes",Sheet2!e:e,"yes")=1,"yes","no") is counting the number of yes for each sku in each of those three columns.
this portion (bold red) =IF(COUNTIFS(Sheet2!A:A,A2,Sheet2!c:c,"yes",Sheet2!d:d,"yes",Sheet2!e:e,"yes")=1,"yes","no")
is saying that if the total number of yeses in those three columns for that specific sku equals 1 (in other words, is there a yes in each of the three columns) then return "yes" otherwise return "no".
Hope that helps.