I am trying since many hours as of how the statement will come true if 2 criterias are met

Let suppose I have employees name mentioned below David Peter Johnson Ashley

Let suppose If i need to check if employee Name "David" is present on 24-August 2022 and if both conditions are met the statement should return "Available" and vice versa

currently the formula i am using is

=IF(AND(B2=Shee2!F$2:F$2000, A2=Sheet2!B$2:B$2000), "Available!", "Not Available")

If i copy paste the formula in a same sequence of Employee name mentioned the gives correct answer.

However if i write Ashley in Row 3 instead of Johnson it gives incorrect answer. The motive is where-ever in the column if both conditions (i.e. Same Date and "Same Employee Name" are found the statement should return with "Available" in other words If Ashley was either mentioned in Row 3 or Row 15 with the required date the statement should come true/Available.

I hope i am able to clarify. Can someone please please help

CodePudding user response：

So, as a test I set this up:

Which shows the structure returning True, which will allow your if() to return the text "Available".

If it does not work on your sheet, then check the data in cells A2 & B2 as well as the data in column F, as something is not correct.

CodePudding user response：

the Top picture shows the fields from Sheet1 and bottom picture shows the Data of Sheet2,