Struggling with writing a formula for a VLOOKUP

Shanon Stoklosa

Screenshot 2020-01-16 09.11.52.png

Screenshot 2020-01-16 09.14.02.png

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). 

Eric M Oliveira

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. 

• =VLOOKUP("1-4 days", {Reference Grid Range}, 2, false)
• https://help.smartsheet.com/function/vlookup

• =INDEX({Weekly Task Percentage Range}, MATCH(“1-4 days”, {Length of Coverage Range}, 0))
• https://help.smartsheet.com/function/index

• SUMIF({Length of Coverage Range}, "1-4 days", {Weekly Task Percentage Range})
• https://help.smartsheet.com/function/sumif

• =SUMIFS({Weekly Task Percentage Range}, {Length of Coverage Range}, "1-4 days") 
• Note: SUMIFS is utilized if you have multiple criteria.
• https://help.smartsheet.com/function/sumifs

This Help Center article outlines how to utilize cross sheet formulas. https://help.smartsheet.com/articles/2476606-formulas-reference-data-from-other-sheets

If you'd like further assistance on addressing your specific formula please connect with our support team here: https://help.smartsheet.com/contact

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

Cheers,

Eric

Smartsheet Technical Support