Tuesday, April 9, 2013

yogi_Pull Spouse Name From A Table In Another Sheet And Assign Numeric Values per Specified Rule

                                          Google Spreadsheet   Post  #1103
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr 9, 2013
user StepenSeattle1 :(http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/f0As4V1xmwY)
Help creating 2 formulas

I’m struggling to create a couple of formulas for a spreadsheet.  I am hoping someone out there, who is a whiz at Google Docs, can help me out. A link to a “mock” spreadsheet to help explain what I trying to accomplish, is located here:

There are 2 tabs/sheets within my spreadsheet (“Main” and “Spouse”).  The formula I am trying to create would return results into column E in the “Main” sheet.  The formula would first look in column A of the “Main” sheet, then based on the name, would look for a match in column A of the “Spouse” sheet. Once a match is found, it would return the results from column B in the “Spouse” sheet to Column E in the “Main” sheet.

For example with Johnny, the formula would match the spouse name of Lauren from the “Spouse” sheet and populate cell E2 with the name Lauren.  I don’t know how to write a formula to accomplish this for any given name match.

The second formula I am trying to create is actually based on 5 set parameters:
1 = 5
2 = 7
3 = 15
4 = 16
5 = 21

The formula I am trying to create is to populate results into column F in the “Main” sheet.  I want the ability to manually enter the character 1, 2, 3, 4, or 5 into column D in the “Main” sheet, and column F will automatically populate with the corresponding number (see above).  I’m not sure if a table should be set up, or each of the number combinations should be listed in the syntax of the formula itself.  I am lost on this one, too. 

For example, if I enter 4 into cell D5, then the number 16 would automatically populate in cell F5.

Is there someone out there that can help me create these formulas?

Thank you!

following is a solution to the problem