How to Add VLook Up Returned Results?


Hello Excel Superstars,

I am trying to add the results returned from a VLook up formula and I can't figure of how to formulate the formula to return the intended results. 

I have attached a copy of the file to see if it can be done and if not what would be my best solution (formula) to solve the problem.

Any help would be greatly appreciated.



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!



Hi John, my name is Sharon. I apologize for my delayed response. 

Thank you for responding to my question. After moving away from the problem for a minute I decided to break the formula up individually and got the intended results. I wanted a single formula to produce the intended results and I now see how you came up with the single formula and it works prefectly.

I appreciate you breaking down the process so I will know how to approach future formulas.

Thanks again and best regards.
sjohnson (rep: 4) Oct 27, '20 at 9:15 pm
Add to Discussion

Please consider this formula.It doesn't need the lookup.


As you already know, A1 must be a number formatted as text. This will cause Excel to format the result also as text. Instead of the result you will see the formula. To cure this, format the cell as General, then select the cell and press F2 and Enter. Then copy down the formatted cell.



Nice! That's a better, more succinct solution Variatus. Not sure why Sharon needs this calculation but yours is the way to go IMHO. 
John_Ru (rep: 250) Oct 28, '20 at 5:12 am
Thank you, John. The advantage of the solution you offered is that the conversion of the digits is flexible. Perhaps Sharon will let us know if she needs that flexibility.
Variatus (rep: 4208) Oct 28, '20 at 9:15 pm
Hi guys, thank you both ! 

I can use both formulas because they both work for me. Flexibitiy is always a plus.

I appreciate the time you both have given to solve my excel conundrum :)

Best Regards,
sjohnson (rep: 4) Oct 28, '20 at 10:43 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login