Skip to content
  • Try for free

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    I require assistance with multiple criteria VLOOKUP from a reference sheet

    ✭✭✭
    in Formulas and Functions

    Hello,

    I require assistance with multiple criteria VLOOKUP from a reference sheet.

    Here is an excerpt from my table(s)

    Sheet1: Contains SolArea2, Region, HQRegion. The combination of all these 3 fields should populate a name that is maintained in another sheet (Sheet2)

    <>_Approver = SolArea2 + Region + HQRegion

    I need H_Approver, L_Approver and E_Approver in 3 separate columns as there as downstream approval workflows.

    Sheet1.jpg


    Sheet2: Contains the same combinations and the Approver column holds the value which should be populated in 'Hiring Manager', 'Learning Manager' and 'Engagement Manager' of Sheet1.

    Sheet2.jpg


    I'm able to achieve this using IF logic, but I think it will be easier maintenance if done in a separate table that can be referenced.

    Any inputs are appreciated.


    Thanks,

    Ambika

    · Share on FacebookShare on Twitter

    Sign in
    Try for free

    Best Answer

    • ✭✭✭
      Answer ✓

      Hi @Andrée Starå ,

      Thank you so much for the quick response.

      This is amazing! I did not think of this possibility. Its a bit of an overstretch do do the basic copy/paste task, but at least it's an option.

      Thank you so much for sharing this. I will try this out and really hope it works with my use case.


      Thanks,

      Ambika

      · Share on FacebookShare on Twitter

    Answers

    • ✭✭✭

      I was able to create a helper column in both sheets to lookup and populate the value. So I was able to solve my initial question.

      My next question is, if in future I change the Approver Name in sheet2, then all the cell values in sheet one get updated, even the old forms that have been closed which can trigger a notification to the approver. How can I avoid this from happening?


      Thanks,

      Ambika

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭✭

      Hi @Ambika Sujir

      Hope you are fine, you can use a workflow to copy or move the completed row to final result sheet which will be used as an archive, in this way you will avoid any unwanted changes.

      PMP Certified

      bassam.khalil2009@gmail.com

      ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

      · Share on FacebookShare on Twitter
    • ✭✭✭

      Hi @Bassam Khalil ,


      Hope you are well!

      Thank you for sharing your inputs.

      Instead of copying entire row over as an archive, is there a way I can copy values in the cell from <>Approver into a new column in the same sheet so I have reference? In that way, if the name changes in future, the formula will update it only on the correct column and not the new column I created?


      Thanks,

      Ambika

      · Share on FacebookShare on Twitter
    • Community Champion

      Hi @Ambika Sujir

      I hope you're well and safe!

      Please have a look at my post below with a method I developed.

      More info: 

      Lock or Store Date/Value Solution without using Zapier

      Andrée Starå

      Would that work/help?

      I hope that helps!

      Be safe and have a fantastic week!

      Best,

      Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

      ✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

      SMARTSHEET EXPERT CONSULTANT & PARTNER

      Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

      W: www.workbold.com | E: andree@workbold.com | P: +46 (0) - 72 - 510 99 35

      Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

      · Share on FacebookShare on Twitter
    • ✭✭✭
      Answer ✓

      Hi @Andrée Starå ,

      Thank you so much for the quick response.

      This is amazing! I did not think of this possibility. Its a bit of an overstretch do do the basic copy/paste task, but at least it's an option.

      Thank you so much for sharing this. I will try this out and really hope it works with my use case.


      Thanks,

      Ambika

      · Share on FacebookShare on Twitter

    Sign in
    Try for free

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions