Friday, April 6, 2012

yogi_Extract The Cost Of Entities From Another Sheet Where Entity Names Have Spurious Space Characters

Yogi Anand, D.Eng, P.E.                Google Spreadsheet          Post  #494         www.energyefficientbuild.com

user SiPEDESIGNS said: 
FORMULA, SCRIPT OR ARRAY? 
 here is the scenario: I have created a data validation list with a dozen different text item's such as "PERFORM SERVICE ONE". In Column A i have multiple rows with different items from this validation list. What i need to do is every time a new service is added to a row in Column A from the validation, in Column D the proper LABOR RATE ($19.95 etc.) from the proper cell in another sheet i have in this workbook called LABOR WORKSHEET is Displayed. If anyone could point be in the direction i would greatly appreciate it.
 - SiPE 
--------
here is editable spreadsheet: https://docs.google.com/spreadsheet/ccc?key=0ApY7Wd9KkdKbdGV6U3dqYS1uRW1XNjRJX2xjRy1ndVE 
------------------------------------------------------
 in user's data there are spurious space characters in the Entity names ... so the normally applicable VLOOKUP formulation will not work here following is my proposed solution to the problem