combine vlookup formula in one row
This may be two questions in one, as match index may be a better solution but right now I only know how to use VLOOKUP.
I have two sheets pulling data from other sheets, one tracks number of routes run as Lead or Second for a particular route; the other tracks the patroller QUAL for each route ' L, S, T, -,'
I would like to combine the results of Route Counts (3L - 1S) and Quals ( L ) into one row, ' 4 - 2 L ' with the intent of conditional formatting so that route builders can see a name/route/color to determine the position when building teams.
=VLOOKUP([email protected], {RouteCounts}, 4, false) Sheet 1, the highlighted cell is [Patroller]
=VLOOKUP([email protected], {RouteQuals},2, false) Sheet 2, the highlighted cell is [Patroller]
both work independent, but I cant combine? I do need to add a "/" or " " in between the formulas
Essentially I would like to produce one sheet that shows the count with the qual.
The above sheets are still under construction
Best Answers
-
Victoria_Indimar ✭✭✭✭
Hello! Yes you can combine the output of those two formulas in one cell. It'll depend on how you want the combined result to look but you could do something like:
=VLOOKUP(example) + " - " + VLOOKUP(example2)
I would also recommend using the INDEX-MATCH instead. Just replace your VLOOKUP formulas with this:
VLOOKUP([email protected], {RouteCounts}, 4, false) --> INDEX({RouteCounts_output column},MATCH([email protected], {RouteCounts_Patroller column},0),1)
VLOOKUP([email protected], {RouteQuals},2, false) --> INDEX({RouteQuals_output column},MATCH([email protected], {RouteQuals_Patroller column},0),1)
-
Victoria_Indimar ✭✭✭✭
It is the Column Index which is optional so how you have it is the same if you are selecting a single column range. :)
Answers
-
solved with INDEX MATCH, adding color condition for easy reference
-
Genevieve P. Employee AdminAha! Index(Match is one of my favourite combos. Glad to see you sorted it out
-
Victoria_Indimar ✭✭✭✭
Hello! Yes you can combine the output of those two formulas in one cell. It'll depend on how you want the combined result to look but you could do something like:
=VLOOKUP(example) + " - " + VLOOKUP(example2)
I would also recommend using the INDEX-MATCH instead. Just replace your VLOOKUP formulas with this:
VLOOKUP([email protected], {RouteCounts}, 4, false) --> INDEX({RouteCounts_output column},MATCH([email protected], {RouteCounts_Patroller column},0),1)
VLOOKUP([email protected], {RouteQuals},2, false) --> INDEX({RouteQuals_output column},MATCH([email protected], {RouteQuals_Patroller column},0),1)
-
Thanks, question what does the "},0)1)" at the end of the index formula do, different then how I have it as "},0))"
-
Victoria_Indimar ✭✭✭✭
It is the Column Index which is optional so how you have it is the same if you are selecting a single column range. :)
Help Article Resources
Categories
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-26T16:26:53+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":18,"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":[]},{"discussionID":106883,"type":"question","name":"Needing some help with my current smartsheet project","excerpt":"So I'm coming across some issues with my workflows and functions with my current sheet, and I'm hoping somebody could help me out because I'm stumped. There are boxes I have set up on children rows that get checked manually to confirm a certain portion of the Main Task is complete. I'm currently in search of a way I can…","categoryID":322,"dateInserted":"2023-06-26T13:40:29+00:00","dateUpdated":null,"dateLastComment":"2023-06-26T16:35:15+00:00","insertUserID":162756,"insertUser":{"userID":162756,"name":"SarahI","title":"Sarah","url":"https:\/\/community.smartsheet.com\/profile\/SarahI","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-26T17:07:56+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":162756,"lastUser":{"userID":162756,"name":"SarahI","title":"Sarah","url":"https:\/\/community.smartsheet.com\/profile\/SarahI","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-26T17:07:56+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":39,"score":null,"hot":3375588344,"url":"https:\/\/community.smartsheet.com\/discussion\/106883\/needing-some-help-with-my-current-smartsheet-project","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106883\/needing-some-help-with-my-current-smartsheet-project","format":"Rich","lastPost":{"discussionID":106883,"commentID":382348,"name":"Re: Needing some help with my current smartsheet project","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382348#Comment_382348","dateInserted":"2023-06-26T16:35:15+00:00","insertUserID":162756,"insertUser":{"userID":162756,"name":"SarahI","title":"Sarah","url":"https:\/\/community.smartsheet.com\/profile\/SarahI","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-26T17:07: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-26T15:51:35+00:00","dateAnswered":"2023-06-26T15:23:39+00:00","acceptedAnswers":[{"commentID":382304,"body":"
