Struggling with writing a formula for a VLOOKUP

I am using smartsheets to access the availability of team members to appropriately assign tasks. How this works is that our mentors (2,000 plus) submit a smartsheet form requesting assistance for a certain time period. On the form they have a question that asks them the length of coverage. They have three selections depending what they select will determine the percentage of work it will task. On another sheet I have the various calculations. I need to grab a daily percentage and a weekly percentage. Another area that I need to address is if they select one of the drop downs for length of coverage the percentages would then needs to be determined from another data point (number of students). 


  • Hello,

    Happy to help. If you'd like to grab a daily percentage and a weekly percentage this can be achieved utilizing a SUMIF/SUMIFS (if numeric values) or a VLOOKUP / INDEX(MACTH)) if a text value. For all 4 of these functions, there will need to be a search/criteria value for the formulas to look for in order to take the desired action. 

    For us to provide specific help on which formula is the best and how you could build it you will need to get more specific on the exact result for us to assist. For example, if 1-4 is selected on sheet A, look for 1-4 on sheet B and pull the value that is in the % column. 

    However, as an example lets say you wanted to pull the value 100% from the Weekly Task Percentage if the Length of Coverage is 1-4 days, the formula could look like one of the below if referencing another sheet. 

    This Help Center article outlines how to utilize cross sheet formulas.

    If you'd like further assistance on addressing your specific formula please connect with our support team here:

    Have a wonderful day. Thank you for contacting Smartsheet Support.



    Smartsheet Technical Support

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!