I have a spreadsheet where I am tracking the costs of items by a product ID which happens to be a 4 or 5 digit number. Some of these items are bundles, which I have indicated in a separate cell. I found an old forum that kind of addresses my issue (http://www.excelforum.com/excel-gene...le-cell.html); however, I am still not able to get the formula to work exactly. I am using a version of this formula in Thread #7: =SUMPRODUCT(SUMIF($A$2:$A$5,MID(SUBSTITUTE(F2,",",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(F2,",","")))),1),INDEX($B$2:$D$5,0,MATCH(F1,B1:D1,0)))).

My issues are twofold:
1. This formula does not seem to like working with numbers only. I have found a work around by adding another column with "ID" before the product ID, but it would be great not to have this additional step/data.
2. And this is my main issue- I changed the match part to look up 6 values, but since my ID is of varying lengths, this causes the formula to bring in incorrect amounts. For example, ID40010 brings up the cost for ID4001 because of the number of characters.


The attached is an example of the data with the amended formula in the highlighted columns.
Vlookup formula help.xlsx

Any help is appreciated, especially with issue #2.