I have a situation where a user will provide a comma separated set of numbers and fill in a separate list of lookup costs. Once the user puts in the values a separate cell will return what the total value of that would be, looks something like below:
[User input values][Returned Value of added cost]
Cost lookup cells
- $400 etc
An example of user input might be: [1, 4 ] which should return [$500]
I'm currently stuck on getting an undefined number of comma's to read. It could have no input with no commas that should return $0, or it could be 200 different values separated by commas (or comma and space like the above example).
My thought process was to take the comma separated values and break each number into a new cell, then using a VLOOKUP to get the cost amount, and add it all up to have a return value. This would all work if I can get some formula to break out an undefined number of values separated by comma's. Something like:
Is this even possible with formula's? The user shouldn't have to do anything besides filling in the lookup cells and putting in what numbers they need.