Formula help: VLookup Google Sheet to Smartsheet

jasmine.pena

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

Adrian Mandile CHESS

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.

jasmine.pena

@Adrian Mandile CHESS: Thanks so much for taking the time to answer this question. Your response is very helpful and thought out. Since posting my team decided to go a different direction from the sheet I was asking about — which honestly I'm glad cause my brain hurts trying to figure it out. 🤣.

I'm confident you answer will helps someone else in the future who stumbles across the same template and tries to convert it to Smartsheet though.