CODEWITHSUNDEEP
excelexcel-formula
user8434445
user8434445

Reputation:

Excel Formula to check if someone's name is in another column and their IP address is not recognised

I'm have a sheet on excel with a list of names and on another sheet I have the same list of names but with duplicates. I also have another column with IP addresses that have been recognized and not recognized so this column prints out a "yes" or a "no".

I want to focus on the Ip addresses that are not recognised for people.

For example:

              Employee Name     IP
                Gary            No
                Sam             Yes

If that person comes up more than ones and there is only 1 "No" in the IP column I want it to print out "No" and if there is a "yes" I want it to print out "yes".

I've copied in the formulas below which I'm trying to use but is coming up with a #NAME? and #V error:

=IF('Data'!A:A='Employee Details'!A2,Yes, No)

=SUMPRODUCT(('Data'!A:A='Employee Details'!A2)*('Employee Details'!H:H=H2)('Data'!G:G))

If anyone could advise where I am going wrong, I'd really appreciate it.

Thanks

Upvotes: 1

Views: 253

Answers (1)

Wyatt Shipman
Wyatt Shipman

Reputation: 1789

You can utilize the COUNTIFS() function to check for multiple criteria in a data set and pair it with and IF() to get your desired outcomes like this:

=IF(COUNTIFS(DATA!A:A,EMPLOYEE_DETAILS!A2,DATA!B:B,"No")>0,"No",IF(COUNTIFS(DATA!A:A,EMPLOYEE_DETAILS!A2,DATA!B:B,"Yes")>0,"Yes","Unknown"))

This formula checks to see if the name in EMPLOYEE_DETAILS!A2 is in the Name column in the DATA sheet AND there is more than 0 No Values in the DATA Sheet in the IP column. Then it checks the same but for Yes, and then returns Unknown if it isn't in the DATA sheet matching those two scenarios.

Upvotes: 0

Related Questions