+ Reply to Thread
Results 1 to 12 of 12

Formula with lots of conditions

  1. #1
    Registered User
    Join Date
    08-12-2013
    Location
    England
    MS-Off Ver
    Mac Excel 2011
    Posts
    27

    Formula with lots of conditions

    I would like to enter a formula into one cell that says:

    if B24 = "White" and B19= "White" then £12. If B24= "White" and B19= "Black" then £20. If B24="White" and B19= "Gold" then £25. If B24= "Gold" and B19= "Gold" then £12. if B24= "Gold" and B19 = "White" then £16 etc etc.

    Is this possible?

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Formula with lots of conditions

    if(and(B24 = "White",B19= "White"),12,If(and(B24= "White",B19= "Black"),20,If(and(B24="White",B19= "Gold")25,If(and(B24= "Gold",B19= "Gold"),12,if(and(B24= "Gold",B19 = "White"),16,if(and(etc,etc)))))))

  3. #3
    Registered User
    Join Date
    08-12-2013
    Location
    England
    MS-Off Ver
    Mac Excel 2011
    Posts
    27

    Re: Formula with lots of conditions

    Thank you!!

  4. #4
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Formula with lots of conditions

    hi amy1, welcome to the forum. do up a table like i did in G19:J23. in column I, i did a concatenation of Column G & H. then put in the values in Column J. your formula would be in D21:
    =IFERROR(VLOOKUP(B24&B19,$I$19:$J$23,2,0),"")
    Attached Files Attached Files

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Formula with lots of conditions

    Hi and welcome to the forum

    daffodil's suggestion will work just fine for you, if you dont have too many more conditions to test for. Although you can have a ton of nested IF()'s, another option might be to create a table of combinations/values and then use a vlookup to return what you want. Something like...

    WhiteWhite...12
    WhiteBlack...20
    WhiteGold...25
    GoldGold...12
    etc

    edit: ben beat me to it (see, told you it was a good idea lol)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Registered User
    Join Date
    08-12-2013
    Location
    England
    MS-Off Ver
    Mac Excel 2011
    Posts
    27

    Re: Formula with lots of conditions

    Thank you all for your replies. I have used daffodil's suggestion but for another very similar formula in the same spreadsheet there are lots more conditions so I'd like to use the table for this. I am a little confused how to enter the formula. I have made the table on another sheet within the same workbook and named the table (if that helps). Can you please break down how to enter the formula?

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Formula with lots of conditions

    Do you have a sample to share?

  8. #8
    Registered User
    Join Date
    08-12-2013
    Location
    England
    MS-Off Ver
    Mac Excel 2011
    Posts
    27

    Re: Formula with lots of conditions

    It's very similar to the above conditions just with more of them. Have added a screenshot of the start of the table, will be much more in it but you get the idea.

    I just need to understand how to construct the formula to add it to the cell.

    Screen Shot 2013-08-12 at 19.05.12.png
    Last edited by amy1; 08-12-2013 at 02:08 PM.

  9. #9
    Registered User
    Join Date
    08-12-2013
    Location
    England
    MS-Off Ver
    Mac Excel 2011
    Posts
    27

    Re: Formula with lots of conditions

    Quote Originally Posted by benishiryo View Post
    hi amy1, welcome to the forum. do up a table like i did in G19:J23. in column I, i did a concatenation of Column G & H. then put in the values in Column J. your formula would be in D21:
    =IFERROR(VLOOKUP(B24&B19,$I$19:$J$23,2,0),"")
    Can someone please explain what the red section is. I don't know how to write the formula.

    What are the $ symbols and what do the last two numbers (2,0) refer to?
    Last edited by amy1; 08-13-2013 at 07:36 AM.

  10. #10
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Formula with lots of conditions

    The dollar symbols means that the cell references (row and/or column) will not change when the formula is copied down or across. As this refers to a table containing data to be looked up, you always want to refer to the same references when the formula is copied. The third parameter, 2, means return the corresponding data from the second column of the table, i.e. look for the combined value B24&B19 within column I (cells 19 to 23), and if a match is found then return the data from the next column (J). The zero at the end of the formula (could also be FALSE), means that you are looking for an EXACT match only.

    Hope this helps.

    Pete

  11. #11
    Registered User
    Join Date
    08-12-2013
    Location
    England
    MS-Off Ver
    Mac Excel 2011
    Posts
    27

    Re: Formula with lots of conditions

    Quote Originally Posted by Pete_UK View Post
    The dollar symbols means that the cell references (row and/or column) will not change when the formula is copied down or across. As this refers to a table containing data to be looked up, you always want to refer to the same references when the formula is copied. The third parameter, 2, means return the corresponding data from the second column of the table, i.e. look for the combined value B24&B19 within column I (cells 19 to 23), and if a match is found then return the data from the next column (J). The zero at the end of the formula (could also be FALSE), means that you are looking for an EXACT match only.

    Hope this helps.

    Pete
    Thank you so much for the help! I understand it completely now.

    Amy

  12. #12
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Formula with lots of conditions

    Well, that's good to hear, Amy.

    VLOOKUP is a very useful function in Excel, so it will be very beneficial to understand how it works.

    Pete

+ 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. [SOLVED] Excel Formula - Lots of ORs and ANDs
    By nikolasm in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-12-2013, 04:48 PM
  2. Formula with lots of variables
    By tara_ionutz in forum Excel General
    Replies: 0
    Last Post: 11-23-2011, 03:15 PM
  3. Lots of graphs, lots of text boxes needed
    By sarasbluegroove in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-28-2011, 09:11 AM
  4. If command with lots of conditions
    By adamtomlinson in forum Excel General
    Replies: 3
    Last Post: 06-22-2011, 08:13 AM
  5. [SOLVED] IF formula question:LOTS clearer to write it
    By Rubix in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-08-2006, 03:40 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