0

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 enter image description here

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

Sheet2

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.

  • Your explanation doesn't match with your screenshot. Simply show your input and desired output. – Harun24hr Nov 27 '22 at 01:57
  • If you are sharing the formulas please provide the screenshot that shows the column letters and rows, so we can identify in the formula the portion of your screenshot. Thanks – David Leal Nov 27 '22 at 03:58
  • 1
    `=SUM(XLOOKUP(TEXTSPLIT(C3,", "),Sheet2!B2:B35,Sheet2!A2:A35,0))` should work. This can of course be made to spill results for each entry in column C if desired. – Jos Woolley Nov 27 '22 at 05:39
  • i'm so sorry for not showing a good screenshot hopefully this updated one helps!! @DavidLeal –  Nov 29 '22 at 04:40
  • @JosWoolley, that formula works but if I change the value in the leadership column, the new number does not update. is there any way to get a dynamic formula that updates dependent on the input? –  Nov 29 '22 at 15:20
  • @AlyssaCirrincione I think you should paste some **copiable data**, not a picture, and clarify your expected results. – Jos Woolley Nov 29 '22 at 15:46
  • Is this better? @JosWoolley I tried to create a table –  Nov 30 '22 at 00:35
  • @AlyssaCirrincione Thanks, but not sure what you mean about the formula I posted not responding to changes in the leadership column. It does precisely that. Share an example for which you feel the result is not what you'd expect. – Jos Woolley Nov 30 '22 at 05:32

0 Answers0