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

Find more posts tagged with

Accepted answers

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.