VLOOKUP with more than one contact
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([email protected]= "Booked In",[email protected]= "Work In Progress"), (VLOOKUP([email protected], {Certificates v2.0 Liability}, 13, true)), "")
Best Answer
-
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([email protected], @cell), {Liability}, @cell = "Current") = 0, "Expired", "Current")
thinkspi.com
Answers
-
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.
thinkspi.com
-
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".
thinkspi.com
-
Kirstine ✭✭✭✭✭✭
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 ✭✭✭✭@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 ✭✭✭✭✭✭
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 ✭✭✭✭
@Kristinesounds 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([email protected], @cell), {Liability}, @cell = "Current") = 0, "Expired", "Current")
thinkspi.com
-
Kirstine ✭✭✭✭✭✭
@Paul NewcomeI'm in love with your huge brain! Thanks, that's worked perfectly!!!
-
Paul Newcome ✭✭✭✭✭✭
-
sharkasits ✭✭✭✭
@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 ✭✭✭✭✭✭
@sharkasitsI'm not sure I follow. Are you able to provide a screenshot with some sample data for reference?
thinkspi.com
Help Article Resources
Categories
Hi @Stephanie D<\/a> <\/p> I hope you're well and safe!<\/p> Try something like this.<\/p> =COUNTIF(Status:Status, \"Green\")<\/p> =COUNTIF([% Complete]:[% Complete], \"Half\")<\/p> Did that work\/help? <\/p> I hope that helps!<\/p> Be safe, and have a fantastic week!<\/p> Best,<\/p> Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p> ✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":106897,"type":"question","name":"Delinquency Report","excerpt":"Hi SmartSheet Community, How do you pull a delinquency report showing month to month stagnation, in the same column across sheets? I'd like to send a monthly update request form for the \"assign to\" with their lists and pull only those item's whose %complete hasn't been updated in the last 30days. Is there a column specific…","categoryID":322,"dateInserted":"2023-06-26T15:35:15+00:00","dateUpdated":null,"dateLastComment":"2023-06-26T16:23:29+00:00","insertUserID":162550,"insertUser":{"userID":162550,"name":"Avani","title":"Senior Manager, PMO","url":"https:\/\/community.smartsheet.com\/profile\/Avani","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-26T18:44:26+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-26T16:21:56+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":19,"score":null,"hot":3375590924,"url":"https:\/\/community.smartsheet.com\/discussion\/106897\/delinquency-report","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106897\/delinquency-report","format":"Rich","lastPost":{"discussionID":106897,"commentID":382339,"name":"Re: Delinquency Report","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382339#Comment_382339","dateInserted":"2023-06-26T16:23:29+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-06-26T16:21:56+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-26T16:27:10+00:00","dateAnswered":"2023-06-26T16:23:29+00:00","acceptedAnswers":[{"commentID":382339,"body":" You would need to use a Record A Date automation to grab the date the % Complete column was last updated.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
