Excel Formula IF, AND, OR to a range???

[Tenal]

Hi all
I am wondering if anyone can assist with creating a formula that will come back with a 'yes' or 'no', if all conditions have been met when linked to a range of cells.
In the basic example above, I want column F to come back with a 'Yes' only if the adjoining cells B-E data can all be found in the table columns G-I. In the above example only Refs 2, 3, and 22 should come back with a Yes (orange colouring indicating condition not met).
The criteria in columns H & K are variable and will change over time so the formula would need to reference the range rather then hard coding the dates/names into the formula.
Any assistance would be appreciated. Worst case is that i can do this by creating four formulas checking they meet the criteria and then a fifth formula to check that all four original formulas have been met but i was hoping for a more elegant solution.
Many thanks
Tenal

https://www.dropbox.com/s/rkuwmzwbg1rreoh/Mr Excel Help.xlsx?dl=0

 

[etaf]

=and(countif(h:h,b2)>0,countif(i:i,c2)>0,countif(j:j,d2)>0,countif(k:k,e2)>0)

would give a TRUE / FALSE

then add an IF

=IF ( and(countif(h:h,b2)>0,countif(i:i,c2)>0,countif(j:j,d2)>0,countif(k:k,e2)>0) , "yes", "no")

 

[Tenal]

Thanks etaf, the formula works which is what counts. For my own benefit and future knowledge, what is the '>0' element doing?
I tested and I think the formula still works without it but I am not 100% sure.
Many thanks

 

[etaf]

yes it will work without it , because a number above 0 is seen as a TRUE
But I wanted to make sure that ALL are greater than zero

=countif(h:h,b2)>0

is counting the column H to see if it contains the value in B2 and counts how many times

by putting the >0
now means its got to return a TRUE or FALSE
TRUE if greater than a zero
False if a zero

it just happens that anything above zero is seen as TRUE anyway
I just like to see the formula in full

also you may have wanted say duplicates where there are 2 or more entries
in which case you would put

=countif(h:h,b2)>1

hope that clarifies , if not please do not hesitate to reply