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

  1. $100
  2. $200
  3. $300
  4. $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:

enter image description here

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.

1 Answer 1


This array formula works with numbers for CO# IF string then you must remove the -- in the formula.

Put this in D2:

=IF(LEN($E$1)-LEN(SUBSTITUTE($E$1,",",""))+1>=ROW($A1),INDEX(A:A,MATCH(--TRIM(MID(SUBSTITUTE($E$1,",",REPT( " ",99)),(ROW($A1)-1)*99+1,99)),$A:$A,0)),IF(ROW($A1)=LEN($E$1)-LEN(SUBSTITUTE($E$1,",",""))+2,IF(COLUMN(A$1) = 1, "$",SUM(INDEX($B:$B,MATCH(N(IF(1,--TRIM(MID(SUBSTITUTE($E$1,",",REPT( " ",99)),(ROW(INDEX($AAB:$AAB,1):INDEX($AAB:$AAB,LEN($E$1)-LEN(SUBSTITUTE($E$1,",",""))+1))-1)*99+1,99)))),$A:$A,0)))),""))

Confirm with Ctrl-Shift-Enter instead of Enter. Then copy over one column and down sufficient to handle all needed rows.

Depending on where in the list it is, the various IF statements, it will split the string and return on or all the parts to the MATCH, which in turn returns all or one of the relative location to the INDEX. Then it either sums up all the parts or returns the correct single value.

It will auto size as needed.

enter image description here

enter image description here

  • Wow, I can't say I understand everything you did with that formula... but it does exactly as I need it to. Some makes sense, but this is intense. Would it be possible to get a breakdown so I can learn what's going on? I used this in conjunction with =LOOKUP(2,1/(E:E<>""),E:E) to catch the total value and use it elsewhere in my file. Thank you very much. Apr 5, 2018 at 21:14
  • select the cell with the formula and use the evaluate formula on the formulas tab. it will step through the formula. Apr 5, 2018 at 21:23

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .