Google Spreadsheet Post #1155
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. Apr 25, 2013
user Ludvig Hult :(http://productforums.google.com/forum/?zx=crpyw0e3sdva#!category-topic/docs/spreadsheets/27xD9fCuTE0)
Nifty way to do lookups and sum with input as a string?
Hi!
Im trying to create a smart formula for doing some lookpus. If i could do a FOR loop or such, i would have no problem, but im kind of new to thinking in spreadsheet formulas...
My input is non-negative integers separated by commas, given in a cell. Say A1 holds that input. for example "200, 234, 1919, 31919".
In column B and C i have key-value pairs. Keys are integers and values are floats.
Now i want to sum the values in column C corresponding to the keys in B that occurs in the input A1.
I tried this:
=ArrayFormula(sum(not(iserr(
it works quite nice, but if the key "200" occurs, and i have the input "2000", it will contribute to the sum because of the find-method behaviour. Is there any good workaround?
---
oh.
and here is an example sheet!
https://docs.google.com/
------------------------------------------------------------------------------------------------------------------
subsequent to a lively discussion in Google Docs User Forum in the following I present Hyde/Ludvig formula
which works for the case in the original post and some alternate solutions for a bit more generalized case
No comments:
Post a Comment