+ Reply to Thread
Results 1 to 11 of 11

Multiple ifs to return more than true or false statement

  1. #1
    Registered User
    Join Date
    03-04-2016
    Location
    Crawley
    MS-Off Ver
    Microsoft Office Professional Plus 2010
    Posts
    5

    Multiple ifs to return more than true or false statement

    I hope I explain my problem fully
    Below is my equation and it works fine, BUT
    =IF((AND(A2="55mm",B2="MDPE",C2="SDR11",D2="LP")),Table!C3,"Error")

    Cell A2, B2, C3 & D4 are all drop down lists
    Cell E2 is where the result is shown
    Send to Forum.jpg

    As you can see I am currently getting a result, if I change cell A2 to 63mm (in the drop down list) the result in cell E2 changes to "Error" which is correct based on the current formula.
    The result in cell E2 is taken from another worksheet (Table) Cell C3) within this workbook.
    I have 24 different diameters in the Worksheet and they are all on in the drop down list.
    So if I choose 55mm I get cell C3, if I choose anything else I get the result "error"
    What I want the formula to do is if I choose 63mm in the drop down list it picks up Cell C4, if I choose 75mm it should pick up cell C5, some people will say just write a look up formula but it is not that simple, because if I change the drop down for SDR rating to 17.6 it needs to pick up a different cell all together, based on the diameter in column A.

    I have tried writing the formula as =IF((AND(A2="55mm",B2="MDPE",C2="SDR11",D2="LP")),Table!C3,"Error"),IF((AND(A2="63mm",B2="MDPE",C2="SDR11",D2="LP")),Table!C4,"Error") but the cell E5 returns this error #VALUE!
    Help
    My drop down list and the worksheet table
    Table.jpg
    Dropdown Lists.jpg

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Multiple ifs to return more than true or false statement

    Welcome to the Forum !

    It would really be better if you attach your file. It is so much easier just to attach your file instead of dealing with all those screen shots. And then we can test solutions on your data.

    Based on what you posted I would try this:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I don't understand your explanation of why you can't use a lookup formula.
    because if I change the drop down for SDR rating to 17.6 it needs to pick up a different cell all together, based on the diameter in column A.
    Your formula does not take into account other possible values for SDR Rating.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,391

    Re: Multiple ifs to return more than true or false statement

    Vince,

    Try the attached, which uses a straightforward "Index and Match Two Criteria" method.

    In B5, select your External Diameter from the DropList (list is whatever you have in Col J).
    In C5, select your SDR Rating from the DropList (list is whatever you have in L2 - P2).

    D5 will then show the Internal DIameter automatically, using this formula:

    =IF(OR(B5="",C5=""),"",INDEX(K:K,MATCH(B5,J:J,0)))

    (I.e, if you have not entered either the External Diameter or SDR rating, it stays blank, If you have entered them, it looks in col K, and shows the match with B5.

    E5 will then show the Cross-sectional area, using this formula:

    =IF(OR(B5="",C5=""),"",INDEX(L:P,MATCH(B5,J:J,0),MATCH(C5,L2:P2,0)))

    (I.e, if you have not entered either the external diameter or SDR rating, it stays blank, If you have entered them, it looks in Cols L - P for whichever row matches the diameter in B5, and whichever column matches the SDR rating in C5).

    Hope that helps

    Ochimus
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    03-04-2016
    Location
    Crawley
    MS-Off Ver
    Microsoft Office Professional Plus 2010
    Posts
    5

    Re: Multiple ifs to return more than true or false statement

    Ochimus,

    Thank you for the file I will have a look and see how it goes.
    I have attached my workbook, it has 3 tabs the calculator tab is where the equation would go in.Upload Vinces New Gas Volume calculator.xlsx

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,430

    Re: Multiple ifs to return more than true or false statement

    Using Ochimus' solution of using Index/Match as a template here are formulas for F5, G5 and H5 respectively:
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    Here is a partial formula for I5 (LP and MP):
    Please Login or Register  to view this content.
    Notice that in the table I made some adjustments to the column header cells (row 3) to remove the space between the alphabetic (SDR) and numeric portions of the rating to match the way the rating is entered in D5 of the calculator sheet.
    Here is a copy of the your file with the formulas and changes applied: Copy of Upload Vinces New Gas Volume calculator.xlsx
    Let me know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  6. #6
    Registered User
    Join Date
    03-04-2016
    Location
    Crawley
    MS-Off Ver
    Microsoft Office Professional Plus 2010
    Posts
    5

    Re: Multiple ifs to return more than true or false statement

    I have tried the first index formula and yes it works, my table that it looks to though is a lot bigger I have only included a small portion, so I changed "Table!A4:A6" to Table!A4:AE35, and this just returns #N/A

  7. #7
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Multiple ifs to return more than true or false statement

    Did you also change "Table!H4:L6" to "Table!H4:L35"?

    The first argument to INDEX is the array where you want to index into. The second argument is the row number. If MATCH returns a number that is larger than the number of rows in the array, you will get an #N/A error.

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,430

    Re: Multiple ifs to return more than true or false statement

    Quote Originally Posted by Vince1199s View Post
    I changed "Table!A4:A6" to Table!A4:AE35, and this just returns #N/A
    Table!A4:A6 is a reference to cells in the same column whereas Table!A4:AE35 is a reference to cells in 31 different columns. In the formula Table!A4:A6 is used to match the diameter given in C5 of the calculator to one of the diameters given in column A of the table so I am guessing you may have meant you changed to Table!A4:A335 in which case you would need to change the first formula, as 6StringJazzer points out, to
    Please Login or Register  to view this content.
    Let me know if you have any questions.

  9. #9
    Registered User
    Join Date
    03-04-2016
    Location
    Crawley
    MS-Off Ver
    Microsoft Office Professional Plus 2010
    Posts
    5

    Re: Multiple ifs to return more than true or false statement

    Quote Originally Posted by JeteMc View Post
    Table!A4:A6 is a reference to cells in the same column whereas Table!A4:AE35 is a reference to cells in 31 different columns. In the formula Table!A4:A6 is used to match the diameter given in C5 of the calculator to one of the diameters given in column A of the table so I am guessing you may have meant you changed to Table!A4:A335 in which case you would need to change the first formula, as 6StringJazzer points out, to
    Please Login or Register  to view this content.
    Let me know if you have any questions.
    Thank you, I have changed it to this now and it picks up all the diameter sizes
    =INDEX(Table!N4:R35,MATCH(C5,Table!A4:A35,0),MATCH(D5,Table!H3:L3,0))

    I am having trouble with this formula though =IF(LEFT(E5,2)="LP",INDEX(Table!S4:W6,MATCH(C5,Table!A4:A6,0),MATCH("*"&D5&"*",Table!S3:W3,0)),IF(LEFT(E5,2)="MP",INDEX(Table!X4:AB6,MATCH(C5,Table!A4:A6,0),MATCH("*"&D5&"*",Table!X3:AB3,0))))

    I have changed it to this
    =IF(LEFT(E5,2)="LP",INDEX(Table!S4:W6,MATCH(C5,Table!A4:A6,0),MATCH("*"&D5&"*",Table!S3:W3,0)),IF(LEFT(E5,2)="MP",INDEX(Table!X4:AB6,MATCH(C5,Table!A4:A6,0),MATCH("*"&D5&"*",Table!X3:AB3,0)),IF(LEFT(E5,2)="IP",INDEX(Table!AC4:AC6,MATCH(C5,Table!A4:A6,0),MATCH("*"&D5&"*",Table!X3:AB3,0)))))

    Where this is pulling the information from has 35 rows so how do I get it to pick up the rows after row 6 please?

  10. #10
    Registered User
    Join Date
    03-04-2016
    Location
    Crawley
    MS-Off Ver
    Microsoft Office Professional Plus 2010
    Posts
    5

    Re: Multiple ifs to return more than true or false statement

    Its ok I have finally figured out where I went wrong
    New formula looks like this =IF(LEFT(E5,2)="LP",INDEX(Table!S4:W26,MATCH(C5,Table!A4:A26,0),MATCH("*"&D5&"*",Table!S3:W3,0)),IF(LEFT(E5,2)="MP",INDEX(Table!X4:AB26,MATCH(C5,Table!A4:A26,0),MATCH("*"&D5&"*",Table!X3:AB3,0)),IF(LEFT(E5,2)="IP",INDEX(Table!AC4:AC26,MATCH(C5,Table!A4:A26,0),MATCH("*"&D5&"*",Table!AC3:AC3,0)))))

  11. #11
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,430

    Re: Multiple ifs to return more than true or false statement

    If that was the last of the issues then we are happy to have been of assistance. If you haven't already please take a moment to mark the thread 'Solved' using the thread tools link above your first post. I hope that you have a good day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Return True or False based on multiple timestamps
    By s474810 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-03-2016, 03:51 AM
  2. IF Statement with Multiple Conditions and Multiple True/False Results
    By ellesutgr in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-03-2015, 04:51 PM
  3. [SOLVED] Comparing Multiple columns across worksheets and returning a True/False statement
    By RoryK in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-11-2015, 10:33 PM
  4. Replies: 2
    Last Post: 05-26-2015, 07:29 PM
  5. IF statement with multiple true false variables
    By tommyt34uw in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-29-2014, 01:17 PM
  6. How to return a value instead of TRUE/FALSE with IF,OR,AND statement
    By jgoat23 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-14-2014, 10:30 AM
  7. Replies: 9
    Last Post: 01-21-2013, 01:54 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1