I am trying to create various numeric values to better track my work rather than simply based on hours, i.e. a particular category of work is assigned more "points" than another category of work based on some subjective difficulty. I've, therefore, created a Google Sheets spreadsheet but to calculate all this.
I have two spreadsheets, one for my main data and the other being a reference spreadsheet where I could list categories and associated grades (and points) which would hopefully work with the main spreadsheet. To better describe this, I've attached some pictures.
In the first sheet, Sheet A, I have two columns, Work Points and Work Categories as follows:
And a reference spreadsheet, Sheet B, as follows:
What I am trying to achieve is as follows:
- Based on the comma-separated list in the Work Categories column in Sheet A, split these and map them to their respective Task in the reference spreadsheet.
Each Task has an associated Grade and each Grade and associated number of points in the Points column, therefore,
- Each mapped Task would have an associated Grade which would then map to the Points column.
- Sum the points based on the values mapped to the comma-separated list and fill a single value for that sum in the Work Points column of the original row (where the formula would be written).
It should be noted that entries in the Work Categories column can be of variable length.
Is this something that is currently possible in Google Sheets? Alternatively, I could use Excel if this is something that is wildly different across platforms.
CodePudding user response：
try:
=ARRAYFORMULA(IF(B2:B10="",,MMULT(IFERROR(
VLOOKUP(VLOOKUP(TRIM(SPLIT(B2:B10, ",")), D:E, 2, ), G:H, 2, ), 0),
SEQUENCE(COLUMNS(SPLIT(B2:B10, ",")), 1, 1, 0))))
