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:
https://docs.google.com/
FORMULA #1
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.
FORMULA #2
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!
Stephen
-----------------------------------------------------------------------------------------
following is a solution to the problem
Hi Yogi,
ReplyDeleteThank you for your help again. I tried to replicate your formulas (as follows) but received parse errors for both. Can you take a look at what I wrote?
FORMULA #1: =ArrayFormula(if(A2:A=””;””;iferror(vlookup(A2:A;Spouse!A:B,2*row(A2:A)^0,0))))
FORMULA #2: =query({1,5;2,7;3,15;4,16;5,21};”select Col2 where Col1=”&D2&” “)
Thanks,
Stephen
Hi Stephen:
ReplyDeleteI think there is a problem with your quote characters
I suggest you copy the formulas directly from my formula sheet into the cells where you need them
or
reproduce the formulas as I have shown them.
Make It A Great One
Cheers!
Yogi
Cloud Computing -- Google Docs Way
yogi--anand-consulting.blogspot.com
Hi Yogi,
ReplyDeleteThe area where you list the formula is embedded as one picture/image. I am unable to highlight your formula within that picture/image. That's why I had to take a stab at writing it out based on what I see. Can you copy and paste the formulas into a response?
Thanks,
Stephen
Never mind, I just figured it out. Thanks!
ReplyDeleteHi Yogi,
ReplyDeleteI have caused an error with one of your formulas and can't figure out how to fix it. It relates to the following array formula:
=ArrayFormula(if(A2:A="";"";iferror(vlookup(A2:A;Spouse!A:B,2*row(A2:A)^0,0))))
I tried to modify this formula for another spreadsheet I’m working on, but it didn't work. As you know, the formula above looks up the name listed in cell A2, then looks for a match of the same name in column A of the 'Spouse' sheet, and if a match is found, pulls the spouse name located in column B of the 'Spouse' sheet into the cell where the formula is being written.
The new spreadsheet I tried to apply this same formula concept to, has the name field in cell A5. I want the formula to look up a matching name in column C of a sheet called 'Customer Database', then pull the contents from column M. The array formula I tried was:
=ArrayFormula(if(A5:A="";"";iferror(vlookup(C2:C;Customer Database!A:M,2*row(A5:A)^0,0))))
This formula produced a parse error. Do you see what I did wrong?
Also, for all these formulas you have helped me with, is there a way for these formulas to automatically carry down to the next row when I insert a new row? Right now, when I insert a new row, I have to manually copy each formula for each column down to the new rows. Is there a way around this?
Thanks so much for your assistance!
Stephen