Formula help: VLookup Google Sheet to Smartsheet

I am trying to get a formula to work in Smartsheet that is outlined in a GoogleSheet and Excel Template for the Scoring Model in Project Management Example Template available on this page. https://www.smartsheet.com/content/project-scoring#project-scoring-criteria-prioritization-template .

The GoogleSheet Template can be located here that shows the example for the formulas and how the are pulled from the other cells on the sheet. https://docs.google.com/spreadsheets/d/1ECb8X3tIgJeIML4lY8pE2hoBOJgAxrWoJh70AJbH0L0/edit?gid=1625686465#gid=1625686465

Can someone help me with getting the formulas to work in Smartsheet after the document is uploaded into that workspace? I keep receiving the #UNPARSABLE error and am not familiar enough with the VLookup formula to correct it.

This is how the current Smartsheet formula looks after it was imported from the Google Sheet

=SUM((VLOOKUP([Column5]5,'EX - Weighted Project Selection'!$Q$6:$[Column18]$9,2,TRUE)*$[Column5]$3),(VLOOKUP([Column6]5,'EX - Weighted Project Selection'!$Q$6:$[Column18]$9,2,TRUE)*$[Column6]$3),(VLOOKUP([Column7]5,'EX - Weighted Project Selection'!$Q$6:$[Column18]$9,2,TRUE)*$[Column7]$3),(VLOOKUP([Column8]5,'EX - Weighted Project Selection'!$Q$6:$[Column18]$9,2,TRUE)*$[Column8]$3),(VLOOKUP([Column9]5,'EX - Weighted Project Selection'!$Q$6:$[Column18]$9,2,TRUE)*$[Column9]$3),(VLOOKUP([Column10]5,'EX - Weighted Project Selection'!$Q$6:$[Column18]$9,2,TRUE)*$[Column10]$3),(VLOOKUP([Column11]5,'EX - Weighted Project Selection'!$Q$6:$[Column18]$9,2,TRUE)*$[Column11]$3),(VLOOKUP([Column12]5,'EX - Weighted Project Selection'!$Q$6:$[Column18]$9,2,TRUE)*$[Column12]$3),(VLOOKUP([Column13]5,'EX - Weighted Project Selection'!$Q$6:$[Column18]$9,2,TRUE)*$[Column13]$3),(VLOOKUP([Column14]5,'EX - Weighted Project Selection'!$Q$6:$[Column18]$9,2,TRUE)*$[Column14]$3))

Answers

  • Adrian Mandile CHESS
    Adrian Mandile CHESS ✭✭✭✭✭
    edited 08/12/24

    Hi @jasmine.pena,

    There are a couple of challenges in this question.

    The first being that the example priority calculation spreadsheet has flawed logic, in that even if a project manager scored every criterion with 5 out of 5, it would still only get a Total Score of 80 out a possible maximum of 100, because the Criteria Score/Score Value range only goes up to 4 = 80%, instead of extending to 5 = 100%. Secondly, the Total Score formula in spreadsheet column 'O', is poorly written even for a spreadsheet.

    CRITERIA SCORE

    SCORE VALUE

    0

    0%

    1

    20%

    2

    40%

    3

    60%

    4

    80%

    CRITERIA WEIGHT
    (Total weight values should equal 100)

    TOTAL
    WEIGHT
    (max 100)

    10

    10

    5

    5

    5

    20

    10

    20

    10

    5

    100

    CUSTOMER VALUE

    BUSINESS VALUE

    CUSTOMER REACH

    CUSTOMER IMPACT

    CUSTOMER CONFIDENCE

    OPERATIONAL COST

    REVENUE VALUE

    BUSINESS RISK

    COST SAVINGS

    OVERALL EFFORT

    TOTAL
    SCORE

    0

    1

    0

    0

    0

    1

    0

    0

    0

    1

    7

    That aside, it is a matrix calculation of a standardised raw score against a weighted criteria set – essentially a type of SUMPRODUCT() calculation, which is straight-forward in a two-dimensional matrix such as an Excel spreadsheet but needs to be thought of a little differently in a data(base) sheet such as Smartsheet.

    The first part of this calculation is to standardise the raw scores given by the project managers against each criterion (in this case 0-to-5 out of a possible maximum of 5) into a percentage. This is done in the spreadsheet using the side lookup Criteria Score/Score Value range but is simply equal to the raw (input) score divided by 5 (maximum). I would create a Sheet Summary field called 'Max Raw Score' and input the maximum score value of 5.

    The second part is about how best to enter/store the set of weightings for each criterion. I would create 10 x Sheet Summary fields with input values that add to 100, named as 'Customer Value', Business Value', etc. for the remaining criteria.

    Assuming that you are using a form to collect the raw score values against each of the 10 criteria for each project, you would also have 10 columns in your sheet named similarly: 'Customer Value', Business Value', etc. I would create these 10 columns as (single choice) 'Dropdown' column types, with options of 0 to 5, displayed as horizontal radio buttons in the form to facilitate scoring.

    Then in a separate 'Total Score' column add the following column formula:

    =SUM(
    [Customer Value]@row * [Customer Value]#,
    [Business Value]@row * [Business Value]#,
    etc. for the remaining 8 criteria
    )
    / [Max Raw Score]#

    NOTE: The "#" denotes the singular global sheet summary field value, while the "@row" denotes the cell value in the row of the specified column.

    Adrian Mandile
    CHESS Consulting Australia - Smartsheet Solution Provider Gold Partner
    Collaborative | Holistic | Effective | Systems | Solutions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!