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

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
SCORE0
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 straightforward in a twodimensional 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 0to5 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
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.1K Get Help
 351 Global Discussions
 198 Industry Talk
 427 Announcements
 4.4K Ideas & Feature Requests
 133 Brandfolder
 127 Just for fun
 127 Community Job Board
 455 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!