Selected Answer

sjohnson (sorry, not sure how to address you)...

(There's something odd about your file so I've recreated it and attached a revision).

Seems to me (from the question in your file) that you're aiming to look up each number in the string from column A then add up those lookup values. (Your current formula seems look the up then weighting but them using multiplier but with the smallest one operating the most significant (leftmost) digit of the number in A (i.e. the opposite of how numbers are nrmally wighted- you may have your reasons!).).

I think there may be problem similar to the one you posted last week- in cell B1 (say), I think you're looking up digits of A1 in the array S1. Trouble is you're using the string functions LEFT and MID to extract text characters for the lookup but looking in S1:S10 which contains numbers not text.

I've changed S1:S1 to text characters (by just adding an apostrophe to 0 in S1 e.g. so it's now '0). Now VLOOKUP(LEFT(A1,1),$S$1:$T$10,2,FALSE) will find the text 0 in S1 and return 100 from T1 (and likewise for each other 0). Taking this further, the B1 formula should be

=SUM(VLOOKUP(LEFT(A1,1),$S$1:$T$10,2,FALSE) + VLOOKUP(MID(A1,2,1),$S$1:$T$10,2,FALSE) + VLOOKUP(MID(A1,3,1),$S$1:$T$10,2,FALSE) + VLOOKUP(RIGHT(A1,1),$S$1:$T$10,2,FALSE))

since this returns 400 (to which you might want to apply custom formatting to - not sure what you're doing with these results since they repeat) and 500 for 0001 in A1 (but also for 0010 in A11).

Not sure why you need such a lookup addition but the maybe a way of using an array formula to get a shorter looking formaula to do the same thing

*P.s. a good way to build up a complex equation is bit by bit (so check that the leftmost character returns the expected results by (in my case) putting *

*= LEFT(A1,1),$S$1:$T$10,2,FALSE *

*in one cell. If that works (e.g. checking with the fx button, next to the formula bar under Homeor the Evaluate Function button under the Formulas ribbon) , move to the second character (in a different cell) and so on. Then combine the successful cell formulae into your one super formula!*