VLOOKUP with more than one contact

Kirstine

I need a formula that when a job is booked in, will 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(Status@row = "Booked In", Status@row = "Work In Progress"), (VLOOKUP(Team@row, {Certificates v2.0 Liability}, 13, true)), "")

Paul Newcome

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(Team@row, @cell), {Liability}, @cell = "Current") = 0, "Expired", "Current")

Paul Newcome

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

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

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

This first screenshot shows the areas of the main sheet that I'm using. If the Status is Booked In or WIP, check the Team and return Current or Expired. The conditional formatting is based on the returned value in Site 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

@Kristine multi 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

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

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

Paul Newcome

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(Team@row, @cell), {Liability}, @cell = "Current") = 0, "Expired", "Current")

Kirstine

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

Paul Newcome

Happy to help. 👍️

sharkasits

@Paul Newcome I 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

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