VLOOKUP with more than one contact

Kirstine · 03/06/23

我需要一个公式,当工作是订了,很快就会回来的l check to see if team members assigned to it have liability insurance. I have a separate sheet (Certificates v2.0) with our workers listed and whether their liability is Current or Expired. The following formula works but only when there is a single team member in the Team column. Obviously when there are two contacts in the cell it no longer matches the list in the Certificates sheet, even with 'True' used in my VLOOKUP. Can anyone help please?

=IF(OR([email protected]= "Booked In",[email protected]= "Work In Progress"), (VLOOKUP([email protected], {Certificates v2.0 Liability}, 13, true)), "")

Paul Newcome · 03/07/23

Right. So you could use a COUNTIFS nested inside of an IF as described before without having to split anything out.

=IF(COUNTIFS({Email Address}, HAS([email protected], @cell), {Liability}, @cell = "Current") = 0, "Expired", "Current")

Paul Newcome · 03/06/23

Try a COUNTIFS instead. You would count how many rows match the criteria, and this allows you to also use a HAS function as well.

Kirstine · 03/06/23

Hi@Paul Newcome, thanks for the speedy response but I don't think this will work. I need the formula to return either Current or Expired which then triggers conditional formatting to highlight if someone on the team's insurance has run out.

Paul Newcome · 03/06/23

Are you able to provide some screenshots for context?

You could still use a COUNTIFS where you count (among the rest of the criteria) how many rows also contain "Current". If that count is at least one, then output "Current", but if the count is equal to zero then "Expired".

Kirstine · 03/07/23

This first screenshot shows the areas of the main sheet that I'm using. If theStatusis Booked In or WIP, check theTeamand return Current or Expired. The conditional formatting is based on the returned value inSite Manager Liability. In this example Bailey is showing correctly as Expired, however Kev & Pete both have their Public Liability insurance so should be showing as Current.

This screenshot shows where I track the teams' certificates and insurances. Bailey is new so has nothing! But Kev and Pete both have 'in date'Public Liability.

sharkasits · 03/07/23

@Kristinemulti contacts get complicated.

Do you have a maximum number of team members assigned to a project?
Are you trying to identify if all team members have liability insurance?

All the mulit selection cells split the items by a return ... Char(10) so you can split them out using a series of REPLACE() and FIND(). The more potential options you have the more complicated the formula gets. If it's a small number of team members, I would created helper columns that split them out and then check that each one has the insurance.

Kirstine · 03/07/23

Thanks@sharkasits, that's a good idea!

There would generally only be one or two team members assigned to a project, and if there are any more they're normally 'external' and we wouldn't be responsible for their liability.

As for your other question, I'd need to know if either of the two team members' insurance had lapsed.

I'll give this a whirl, thank you!

sharkasits · 03/07/23

@Kristinesounds good. Let me know if you run into any issues.

Paul Newcome · 03/07/23

Right. So you could use a COUNTIFS nested inside of an IF as described before without having to split anything out.

=IF(COUNTIFS({Email Address}, HAS([email protected], @cell), {Liability}, @cell = "Current") = 0, "Expired", "Current")

Kirstine · 03/08/23

@Paul NewcomeI'm in love with your huge brain! Thanks, that's worked perfectly!!!

Paul Newcome · 03/08/23

Happy to help.️

sharkasits · 03/28/23

@Paul NewcomeI didn't realize the HAS function worked that way, very cool. Do you know if it defaults to Has Any vs. Has All? And is there a way to toggle between them?

Paul Newcome · 03/28/23

@sharkasitsI'm not sure I follow. Are you able to provide a screenshot with some sample data for reference?

VLOOKUP with more than one contact

Best Answer

Answers

Help Article Resources

Categories