Google Sheets VLOOKUP example: weighted scoring with variable / value assignment

Sometimes I make apps or spreadsheets to help me (or others) make decisions based on specific weighted criteria. It’s hard to find straight-forward, working examples of these kinds of spreadsheets online, so I thought I’d post one.

VLOOKUP weighted scoring sample spreadsheet

A few notes:

  • There are two Sheets
    • Input: You can toggle all of the fields with the white background; the score appears in the right-most column, which is conditionally formatted by score
    • Scoring: Here’s where you weight your criteria. If I had wanted to get really fancy with this, I could have categorized the different criteria and weighted them at the category level and apportioned a % of that weight to the various criteria within the category.
  • The “Input” sheet has a number of hidden columns where calculations are performed; no need to un-hide them unless you have made changes and want to debug something.
  • I strongly recommend calibrating on your methodology by entering a number of sample rows to make sure your weighting on the “Scoring” sheet is producing the scores (in Column P on the “Input” sheet) that you expect.
  • For more info about VLOOKUP and other Google Sheets functions, check out the Docs Editors Help Center article.

Screenshot 2015-11-28 at 10.40.36 PM



Posted in business productivity