I'm creating a spreadsheet where I may have two values in the same cell seperated by commas, such as "Leadership, Activity on Campus". Both of these are worth two different amounts of points. I was trying to create a formula that would allow me to return the sum of both of these and also be able to run the formula again, if a third delimiter was added.
current table
I've tried xlookup, vlookup, i've tried the formulas,
=(IF(ISBLANK(B3),0,9)+XLOOKUP(C3,Sheet2!$B$2:$B$35,Sheet2!$A$2:$A$35)))
=MAKEARRAY(SUM(IFNA(xlookup(trim(TEXTSPLIT(C2,",")),Sheet2!A2:B35,row(Sheet2!A2:35),0)))
=TEXTJOIN(",",TRUE,IFERROR("--",XLOOKUP(FILTER("<t><s>" & (SUBSTITUTE(C3,",","</s><s>") & "</s></t>"), "//s"), Sheet2!$B1, Sheet2!$A1)))
And I'm just really stuck. I'm using a drop down list in my excel sheet so there is only certain values that people may add. I coded in the back to allow for multiple values in the drop down menu.
Sophomores Table | Name | Sport | Leadership | Points | | :---------- | :--------: | :--------------------: | --------: | | John Smith | Basketball | Leadership on Campus | 18 | | Jane Doe | (blank) | Committee Not | 14 | | | | Mentioned, Leadership | | | | | on Campus | |
Sheet2
| Points | Description |
| :---------------------- | -----------------------: |
| 9 | Varsity Sport |
| 9 | Leadership on Campus |
| 5 | Committee Not Mentioned |
The formula is in the Points column. I want each statement in the Leadership column to match the value of Sheet2. For the output of this particular sheet I would expect 18 for John Smith and 14 for Jane Doe. For JOhn Smith I got this number because Basketball is a varsity sport, therefore it is 9 points, plus another 9 points because they are a leader on campus. For Jane Doe I got this value by adding 5 from the committee not mentioned to the 9 from Leadership on campus.