+ Reply to Thread
Results 1 to 14 of 14

Multi IF, Multi OR & Multi AND

  1. #1
    Registered User
    Join Date
    04-10-2010
    Location
    Abu Dhabi, UAE
    MS-Off Ver
    Excel 2003, 2007 and 2010
    Posts
    148

    Thumbs up Multi IF, Multi OR & Multi AND

    Hi,

    How can I write this formula:

    IF(A1=1 and A2=2) then VLOOKUP(C1....) OR IF(B1=3 and B2=4) then VLOOKUP(D1....)

    OR................ otherwise F1
    Last edited by Ralem; 05-31-2010 at 10:02 AM.
    Cheers
    Ralem

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Multi IF, Multi OR & Multi AND

    Something like..

    =IF(AND(A1=1,A2=2),VLOOKUP(C1,....),IF(AND(B1=3,B2=4,VLOOKUP(D1,.....),.......,F1)


    But you don't want to many nested IF's, especially in pre 2007.

    If you detail your problem, someone may be able to help create a more efficient formula.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: Multi IF, Multi OR & Multi AND

    Just a minor correction to NBVC's formula:

    Please Login or Register  to view this content.
    Cheers,
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.

  4. #4
    Registered User
    Join Date
    04-10-2010
    Location
    Abu Dhabi, UAE
    MS-Off Ver
    Excel 2003, 2007 and 2010
    Posts
    148

    Re: Multi IF, Multi OR & Multi AND

    Hi,

    Thanks for the help, well... I really need to use multiple IFs and ORs in XL 2003, below is my formula; can we shorten it somehow to be able to use more IFs:

    =IF(AND($H$5="g",AK5=AB2),VLOOKUP(AB2,$AK$5:$AN$105,3,FALSE),IF(AND($H$9="g",AK5=AC2),VLOOKUP(AC2,$AK$5:$AN$105,3,FALSE),IF(AND($H$13="g",AK5=AD2),VLOOKUP(AD2,$AK$5:$AN$105,3,FALSE),IF(AND($H$17="g",AK5=AE2),VLOOKUP(AE2,$AK$5:$AN$105,3,FALSE),IF(AND($H$21="g",AK5=AF2),VLOOKUP(AF2,$AK$5:$AN$105,3,FALSE),IF(AND($H$25="g",AK5=AG2),VLOOKUP(AG2,$AK$5:$AN$105,3,FALSE),NA()))))))

  5. #5
    Registered User
    Join Date
    04-07-2010
    Location
    Vancouver, Washington
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Multi IF, Multi OR & Multi AND

    Hi,
    Is it possible to share a version of the spreadsheet you are using so we can visually see what you are trying to accomplish? I am not sure if you can shorten it down due to the lookup value changing based on each logical test. Also I found when searching that someone may have an answer already to your puzzle with the nested IF's, searched while I was replying here.
    http://www.excelforum.com/excel-gene...e-formula.html

    Basically he is suggesting using some clever calculations outside the nesting, but I am not sure that would help your situation.

    Please Login or Register  to view this content.
    If I have been helpful please click the reputation icon (old fashioned scale) to add to my reputation. Thank you!

    Jim (aka AnalystnotAnal)
    Excel Intermediate
    VB Novice (not a beginner)

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Multi IF, Multi OR & Multi AND

    Quote Originally Posted by Ralem View Post
    Hi,

    Thanks for the help, well... I really need to use multiple IFs and ORs in XL 2003, below is my formula; can we shorten it somehow to be able to use more IFs:

    =IF(AND($H$5="g",AK5=AB2),VLOOKUP(AB2,$AK$5:$AN$105,3,FALSE),IF(AND($H$9="g",AK5=AC2),VLOOKUP(AC2,$AK$5:$AN$105,3,FALSE),IF(AND($H$13="g",AK5=AD2),VLOOKUP(AD2,$AK$5:$AN$105,3,FALSE),IF(AND($H$17="g",AK5=AE2),VLOOKUP(AE2,$AK$5:$AN$105,3,FALSE),IF(AND($H$21="g",AK5=AF2),VLOOKUP(AF2,$AK$5:$AN$105,3,FALSE),IF(AND($H$25="g",AK5=AG2),VLOOKUP(AG2,$AK$5:$AN$105,3,FALSE),NA()))))))
    This formula doesn't really make a great deal of sense to me.

    It basically seems to read that if "g" can be found in any of H5, H9, H13, H17, or H21 and AK5 can be found in AB2:AG2 then to conduct a VLOOKUP based on AK5.

    However given the use of references etc the implication is simply that you wish to return the contents from Column AM - ie if AK5 is found in the header row then return the value in Col AM (given the VLOOKUP of AK5 will simply be the first row)

    Based on the above, perhaps then:

    Please Login or Register  to view this content.
    if the "g" test is repeated on each row then I would suggest calculating just once and referring to the result cell thereafter (ie avoid repetitive calcs.)

  7. #7
    Registered User
    Join Date
    04-10-2010
    Location
    Abu Dhabi, UAE
    MS-Off Ver
    Excel 2003, 2007 and 2010
    Posts
    148

    Re: Multi IF, Multi OR & Multi AND

    Thanks guys,

    but, DonkeyOte, It'snot like: IF any of OR, each IF(AND is one different case...
    Last edited by Ralem; 05-29-2010 at 04:54 AM.

  8. #8
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Multi IF, Multi OR & Multi AND

    The formula provided apes the formula you posted so I'm not really sure as to your point.

    Your current formula makes little sense from a syntax perspective given each VLOOKUP is identical albeit for the criteria which is itself simply the value in AK assuming it is found in row 2.

    I would suggest you post a sample file to better illustrate your question / requirements.

  9. #9
    Registered User
    Join Date
    04-10-2010
    Location
    Abu Dhabi, UAE
    MS-Off Ver
    Excel 2003, 2007 and 2010
    Posts
    148

    Re: Multi IF, Multi OR & Multi AND

    Please find the attachment.
    Attached Files Attached Files

  10. #10
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Multi IF, Multi OR & Multi AND

    I agree with DonkeyOte, as to your formula not making too much sense.

    You have the same code repeated in column L several times... I assume you just want to match the code on the same row?

    To me, this formula: =IF(G5="g",N5,NA()) seems to be what you might want... I am not sure why the Vlookup is even necessary

    If says that if G5 is "g", then just return what is in column N within the same row.. (i.e. the 3rd column from column L.

  11. #11
    Registered User
    Join Date
    04-10-2010
    Location
    Abu Dhabi, UAE
    MS-Off Ver
    Excel 2003, 2007 and 2010
    Posts
    148

    Re: Multi IF, Multi OR & Multi AND

    Hi,

    Please find the attachment, it's the same with some corrections....

    Have you clicked F5 and L5, this is designed to auto calculate Slope by entering numbers 1,2,....or 5 over column D, try to delete and add some of these numbers and notice the calculated slope....
    did you notice that I6 and I7 are N/A coze there is no g in cell G13;

    Slope in cell F5 is calculated from 1 numbers typed in column D... g in cell G5 is related to slope1 data and will show in column I,J and K the data only opposite those ones.... hope this clarify it..
    Actually this is just a part of my spreadsheet, the idea is to apply conditional format on data series on a chart and have to do this coze I'm using excel 2003.
    Attached Files Attached Files
    Last edited by Ralem; 05-30-2010 at 05:25 PM. Reason: Error in the attachment

  12. #12
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Multi IF, Multi OR & Multi AND

    Maybe in I5, then:

    Please Login or Register  to view this content.
    copied down.

    You can have up to 30 AND() conditions in there (in Excel2003).

    use same in other columns.. but adjust the N5 to get your desired number from M:O

  13. #13
    Registered User
    Join Date
    04-10-2010
    Location
    Abu Dhabi, UAE
    MS-Off Ver
    Excel 2003, 2007 and 2010
    Posts
    148

    Re: Multi IF, Multi OR & Multi AND

    Very nice NBVC, thanks a lot

    this will solve for me the limitation of IFs in XL 2003

  14. #14
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Multi IF, Multi OR & Multi AND

    Quote Originally Posted by Ralem View Post
    this will solve for me the limitation of IFs in XL 2003
    Not sure if that is a question or not, but yes, it will solve that limitation as you can use up to 30 AND() conditiions within the OR() functions

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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