VLOOKUP not pulling in correct data when duplicate names

Hello

I have a line of business with three choices: deploy, consult, and managed services, each associated with all theaters. When someone submits a request through the intake form, they select one of these options, and the form populates the data into the Sub_Org column based on the chosen line of business, and theater. However, if a manager's name appears twice, the formula only picks up the data from the top of the list. I need the formula to search the entire list and populate the correct Sub-Org based on the L5 manager and the associated line of business and theater.

How can I correct my current formula to pull the correct sub-org based on the L5 Manager from the lookup list? I am facing an issue, and I'm unsure how to fix it.


Formula using: =VLOOKUP([Last Name, First Name (VP)]@row, {L5 Manager_Sub-Org Lookup}, 2, false)

duplication vlookup not working.png


Adriane

Tags:

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey@Adriane Price

    I'm having trouble following what columns are on what sheets in your data collection. If the different columns are all either on the source sheet or the destination sheet, you should be able to pull in the data using a COLLECT without having to concatenate. You cannot use a cross sheet reference range in Concatenate as you're trying to do.

    If your Lookup sheet now has the additional columns of theater and Line of business, the COLLECT should work for you. After creating cross sheet references to each of the individual columns in your lookup sheet, you should be able to filter against the appropriate criteria. Your ranges might be named differently but the formula will look something like.

    =INDEX(COLLECT({Lookup sheet Sub_Org column}, {Lookup sheet LS Manager}, [Last Name, First Name (VP)]@row, {Lookup sheet Theater column}, Theater@row, {Lookup sheet Line of Business column}, [Line of Business]@row),1)

    What does this get you?

    Kelly

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Hello@Adriane Price

    An Index/Collect lookup allows multiple criteria to be used, vs the single criteria that is used in a VLookUp.


    =INDEX(COLLECT({LS Manager_Sub_Org sheet Sub_Org column}, {LS Manager_Sub_Org sheet Line of Business column}, [Line of Business]@row, {LS Manager_Sub_Org sheet LS Manager column}, [Last Name, First Name (VP)]@row),1)

    我不确定如果这个过滤器就足够了,或者我f you were encountering multiple instances of these same criteria and needed to differentiate amongst them. If this is true, then I use a COUNTIFS instead of the '1' to give me the correct row_index. You can see an example of this methodhere

    Will the formula above work for you?

    Kelly

  • Adriane Price
    Adriane Price ✭✭✭✭✭

    @Kelly Moore- I did see the other method example today but in my ignorance I am not sure that it will work. If I understand with the other method I would need to add in an "ID" type of column to count which L5 manager would be selected for the Sub-Org column?

    In regard to the formula provided above, I received an #INVALID VALUE, which I am not interested in counting but rather pulling in the Sub-Org. Sorry I am not sure I understand.

    =INDEX(COLLECT({LS Manager_Sub_Org sheet Sub_Org column}, {LS Manager_Sub_Org sheet Line of Business column}, [Line of Business]@row, {LS Manager_Sub_Org sheet LS Manager column}, [Last Name, First Name (VP)]@row),1)

    Lookup sheet has two columns of value that I need L5 Manager and Sub-Org, the Sub-Org column is the one I need to pull in based on the L5 Manager name:

    image.png

    When the submitter uses the intake form they select a "line of business" but this is not in my current formula which I think may be what is causing the mismatch between the "line of business" selection, pulling in the correct Sub-Org that is based on the L5 Manager.

    I tried updating to this formula below, but receiving #UNPARESEABLE

    Screenshot 2023-08-05 121649.png

    Formula in second column (testing which formula will work) =INDEX({L5 Manager_Sub-Org Lookup Org}, MATCH(CONCATENATE(Theatre, "-", [Line of Business]@row, "-", [Last Name, First Name (VP)]@row), CONCATENATE({Theatre column}, "-", {Line of Business column}, "-", {L5 Manager column}), 0))

    I need to use two or more criteria when looking up data from the database. There is one person Ramesh MC: who approve for Deploy projects and the other Consult projects. I need to make sure the each Deploy and Consult are unique in the first column of my table. Which I thought I did....I cannot change the Sub-Org because it is system wide just like each line of business and VP name.


    So is where I am having the disconnect between your formula you suggested and the database? I need to create a helper column of sorts?


    I attempted to update my lookup sheet as well by adding in a Theater column and Line of Business:

    image.png


    Adriane

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey@Adriane Price

    I'm having trouble following what columns are on what sheets in your data collection. If the different columns are all either on the source sheet or the destination sheet, you should be able to pull in the data using a COLLECT without having to concatenate. You cannot use a cross sheet reference range in Concatenate as you're trying to do.

    If your Lookup sheet now has the additional columns of theater and Line of business, the COLLECT should work for you. After creating cross sheet references to each of the individual columns in your lookup sheet, you should be able to filter against the appropriate criteria. Your ranges might be named differently but the formula will look something like.

    =INDEX(COLLECT({Lookup sheet Sub_Org column}, {Lookup sheet LS Manager}, [Last Name, First Name (VP)]@row, {Lookup sheet Theater column}, Theater@row, {Lookup sheet Line of Business column}, [Line of Business]@row),1)

    What does this get you?

    Kelly

  • Adriane Price
    Adriane Price ✭✭✭✭✭

    Hello@Kelly Moore- thank you for reading between the lines and figuring out what obviously I could not describe.

    I adapted the formula very slightly to fit all the columns and table I was using. Your formula worked, thank you for the guidance and providing me knowledge to understand.

    =INDEX(COLLECT({L5 Manager_Sub-Org Lookup Org}, {L5 Manager_Sub-Org Lookup L5}, [Last Name, First Name (VP)]@row, {Lookup sheet Theater column}, Theatre@row, {Lookup sheet Line of Business column}, [Line of Business]@row), 1)


    image.png


    Adriane

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭

    Excellent! I’m so glad you got it to work. I encourage you to explore the COLLECT function. Although it must be paired with something, it can be used with so many other functions. I think it is one of the most versatile functions in our arsenal.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out theFormula Handbook template!
Try this:<\/p>

=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/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":109490,"type":"question","name":"HAS exact match within multiselect - Numbered Values","excerpt":"Scenario: Trying to identify a match if a value shows up in a multiselect from another sheet. Approach: I'm able to get 95% of this done through an index(collect(contains))) formula, but having some false positives show up wherever a partial match is found. I later found some suggestions that (has) would be more…","snippet":"Scenario: Trying to identify a match if a value shows up in a multiselect from another sheet. Approach: I'm able to get 95% of this done through an index(collect(contains)))…","categoryID":322,"dateInserted":"2023-08-25T19:26:32+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:49:48+00:00","insertUserID":154049,"insertUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:49:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":154049,"lastUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:49:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":16,"score":null,"hot":3386003780,"url":"https:\/\/community.smartsheet.com\/discussion\/109490\/has-exact-match-within-multiselect-numbered-values","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109490\/has-exact-match-within-multiselect-numbered-values","format":"Rich","lastPost":{"discussionID":109490,"commentID":392694,"name":"Re: HAS exact match within multiselect - Numbered Values","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392694#Comment_392694","dateInserted":"2023-08-26T00:49:48+00:00","insertUserID":154049,"insertUser":{"userID":154049,"name":"Rob W.","url":"https:\/\/community.smartsheet.com\/profile\/Rob%20W.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:49:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/KJPRLKL2FW16\/capture-png.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Capture.PNG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-26T00:49:35+00:00","dateAnswered":"2023-08-25T23:58:23+00:00","acceptedAnswers":[{"commentID":392688,"body":"

Hi, <\/p>

Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>

=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>

Thank you,<\/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":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T14:46:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T14:46:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"

\n \n https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula\n <\/a>\n<\/div>\n

Hi, <\/p>

I hope you're well and safe!<\/p>

Try something like this.<\/p>

=IF([Date Reported]@row <> \"//www.santa-greenland.com/community/discussion/108555/\", IF([Resolution Date]@row = \"//www.santa-greenland.com/community/discussion/108555/\", NETDAYS([Date Reported]@row, TODAY()), NETDAYS([Date Reported]@row, [Resolution Date]@row)))<\/p>

Did that work\/help? <\/p>

I hope that helps!<\/p>

Be safe, and have a fantastic weekend!<\/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"}]}],"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":[]}">

Trending in Formulas and Functions