Wednesday, August 22, 2012

Creating an IF ELSEIF functoin for Google Spreadsheet

Creating an IF ELSEIF functoin for Google Spreadsheet.



Here is what I am trying to do... I have a list of numbers (1-5) in cell A1 on my main sheet that are in the form of a Data Validation drop down menu.  That Data Validation is being generated with the data from cells A1-5 on another sheet named NUMBERS.  Also on the NUMBERS sheet are the numbers 10%-50% in cells B1-5.  I would like to create a function for cell B1 on the main sheet that tells it to display the data from cell B5 on the NUMBERS sheet, if the the number 5 is selected from the Data Validation in cell A1 on the main sheet.

So, if I select the number 3 from the Data Validation in cell A1, then B1 displays the number 30%.  2 returns 20%, 1 returns 10% and so on.

Solution:

You have to put the following formula in cell B1 (of "MAIN" sheet):
=if(A1=NUMBERS!A1,NUMBERS!B1,if(A1=NUMBERS!A2,NUMBERS!B2,if(A1=NUMBERS!A3,NUMBERS!B3,if(A1=NUMBERS!A4,NUMBERS!B4,if(A1=NUMBERS!A5,NUMBERS!B5,)))))

OR

You can also put the following formula in Cell B1 (of "MAIN" sheet) to get your result:
=FILTER(NUMBERS!B1:B5,NUMBERS!A1:A5 = A1)

Following is the screenshot of "MAIN" sheet


And following is the screenshot of "NUMBERS" sheet.




I hope the above solution will help you, and if you need more help then please do comment below on this blog itself I will try to help you out.

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,
Kishan,
iGoogleDrive

4 comments:

  1. ארז כהןAugust 8, 2015 at 10:15 PM

    Kishan, this is useful to an extent. Let me complicate this further:
    On the main spreadsheet I have the list of 1-10 in column A. On Numbers sheet I have 3,5,6,8,10 in column A. and a set of values in column B. I would like to copy the values from Numbers!B to the main sheet on column B based on the equivalent number on Numbers!A.

    ReplyDelete
  2. Zigi PutninsDecember 4, 2015 at 2:05 AM

    use vlookup, with the table being numbera!A:B and the index being main:A

    ReplyDelete
  3. UnknownOctober 27, 2017 at 10:08 PM





    Can someone help me find the fault in this code?






    =if(AND(B2 = "buy",I2 != "NIL"),(if(I2-C2>C2*0.02),(C2+(C2*0.015)),(if(I2-C2>C2*0.0175),(C2+(C2*0.125)),(if(I2-C2>C2*0.015),(C2+(C2*0.01)),(if(I2-C2>C2*0.0125),(C2+(C2*0.0075)),(if(I2-C2>C2*0.01),(C2+(C2*0.005)),"NIL"))))),(if(C2-I2>C2*0.02),(C2-(C2*0.015)),(if(C2-I2>C2*0.0175),(C2-(C2*0.0125)),(if(C2-I2>C2*0.15),(C2-(C2*0.01)),(if(C2-I2>C2*0.125),(C2-(C2*0.075)),(if(C2-I2>C2*0.075),(C2-(C2*0.001)),"NIL"))))))

    ReplyDelete
  4. Prankster AtomMay 29, 2018 at 12:48 PM

    i want to punch you in the face for how fucking stupid your tutorial is, i get it but your whole demonstration is shit.

    ReplyDelete

Subscribe to: Post Comments (Atom)