Thursday, November 1, 2012

yogi_Compute Row By Row Price For SKUs Listed Singly Or As A Group Of SKUs With a Delimiter

                                           Google Spreadsheet   Post  #846
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Nov 01, 2012
user Grikgod said: (http://productforums.google.com/forum/?zx=gdxpen6jynp#!category-topic/docs/spreadsheets/CeB6GRaQEV4)
Looking up a value based on multiple entries in a single cell
Hi guyus,

I am looking for help with creating a formula that can read multiple values inside 1 cell and return the sum of the values it looks up. For a clearer idea of what I am looking to do.

Currently I am using the following formula:

=vlookup(G3,Pricing!A:B,2,false)

This formula allows me to input a value in the G3 cell (let us say 12345) and it returns a dollar amount from the corresponding Pricing Sheet (let us say $50)

I would like to be able to enter in cell G3, about two or more values (e.g. 12345, 6789, 1112) and it returns me the sum of them ($50 + $30 + 45 = $125) instead as I have some cells with multiple entries. So the formula would look up the value for 12345 and add it to the value for 6789, etc, and just give me the sum of their related values?
Basically, Are there any formulas that would allow me to separate the values in 1 cell and do this? I can attach a sample file if I have not made too much sense.
---
So I have posted a sample, and in the sample I have divided the Sku with a / so that it is 6104/5122. I can separate these any way 6104,5122 or "6104" "5122" if needed.
Basically I am looking to solve the #NA error you see on that shared sheet.
Thank you for the quick reply!
-------------------------------------------------------------------------------------
for starters I have created a solution with a number of helper columns -- it is possible the formulas can be consolisated but I will leave that for another time.