+ Reply to Thread
Results 1 to 8 of 8

Nesting If statements w/ Vlookups

  1. 02-02-2009, 02:00 PM #1
    ffmariners
    ffmariners is offline
    Registered User
    Join Date
    09-06-2008
    Location
    Philly
    Posts
    40

    Nesting If statements w/ Vlookups

    Hi,

    I know how to do a vlookup and I know how to do an if statement. I also know how to do an IF(ISERROR or IF(ISNA ....

    However, I can not get this to work:

    =IF(C73<7,VLOOKUP(D73,'Package Mix'!$G$39:$H$45,2,FALSE),IF(C73<13,VLOOKUP(D73,'Package Mix'!$G$6:$H$36,2,FALSE),IF(C73>23,VLOOKUP(D73,'Package Mix'!$G$48:$H$56,2,FALSE),"")))

    I can not wrap my head around how to make this work w/ IF(ISNA ....


    Also, why must you use < or > .... I actually want the numbers to be 6,12, and 24... However, it seems when I use an equal sign it fails. Any way around this?


    Thanks a lot!
    Frank
    Last edited by ffmariners; 02-02-2009 at 02:32 PM.
    Reply With QuoteRegister To Reply
  2. 02-02-2009, 02:13 PM #2
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Nesting If statements w/ Vlookups

    If you only want to to do the VLOOKUP's if column C is 6, 12, or 24, then you should be using =6, =12, or =24. It might not be working because your value is not exactly 6,12 or 24? Is that number the result of a formula? As to why it is not working, I'd need to see an example workbook attached.
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
    Reply With QuoteRegister To Reply
  3. 02-02-2009, 02:15 PM #3
    franciz
    franciz is offline
    Registered User
    Join Date
    12-16-2006
    Location
    Bangkok
    MS-Off Ver
    Excel 2003
    Posts
    98

    Re: Nesting If statements w/ Vlookups

    Is the formula that you post not working or you can't include the ignore error in your formula?
    would you attach a sample of your file removing all confidential data.

    cheers, francis
    Reply With QuoteRegister To Reply
  4. 02-02-2009, 02:17 PM #4
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Nesting If statements w/ Vlookups

    Try:

    Please Login or Register  to view this content.
    The reason why the =6,12 or 24, is that the C73 might be actually be a decimal value...

    maybe changing to INT(C73) may work...
    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.
    Reply With QuoteRegister To Reply
  5. 02-02-2009, 02:23 PM #5
    ffmariners
    ffmariners is offline
    Registered User
    Join Date
    09-06-2008
    Location
    Philly
    Posts
    40

    Re: Nesting If statements w/ Vlookups

    The values are exactly 6, 12, and 24 (they are input using a data validation drop down).

    Don't know why it won't work.

    However... more important is getting the IF(ISNA to work... That is what is bugging me the most!


    Will try to attach a sample when I get home later if we can not solve it without!
    Reply With QuoteRegister To Reply
  6. 02-02-2009, 02:24 PM #6
    DonkeyOte
    DonkeyOte is offline
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Nesting If statements w/ Vlookups

    I would add ...

    what data type resides in H (number, text) ...
    what do you want to return if it's an error...
    what version of Excel are you running...

    Assuming for ex. you're pre 2007... H contains numbers... for error return 0...

    Please Login or Register  to view this content.
    (if you wanted blank rather than 0 you could use a Custom Format to set 0 to null)

    Ditto other comments re: INT
    My Recommended Reading:

    Volatility
    Sumproduct & Arrays
    Pivot Intro
    Email from XL - VBA & Outlook VBA
    Function Dictionary & Function Translations
    Dynamic Named Ranges
    Reply With QuoteRegister To Reply
  7. 02-02-2009, 02:27 PM #7
    ffmariners
    ffmariners is offline
    Registered User
    Join Date
    09-06-2008
    Location
    Philly
    Posts
    40

    Re: Nesting If statements w/ Vlookups

    Beautiful, that works!

    I was doing the ISNA for the whole thing... not each IF statement independently... woops!

    Thanks a lot.

    And I changed the equalities to = signs... and it works. I think the issue was something else as I have never had that problem before.


    Cheers!


    Quote Originally Posted by NBVC View Post
    Try:

    Please Login or Register  to view this content.
    The reason why the =6,12 or 24, is that the C73 might be actually be a decimal value...

    maybe changing to INT(C73) may work...
    Reply With QuoteRegister To Reply
  8. 02-02-2009, 02:30 PM #8
    ffmariners
    ffmariners is offline
    Registered User
    Join Date
    09-06-2008
    Location
    Philly
    Posts
    40

    Re: Nesting If statements w/ Vlookups

    Oh, this works too. Is this a more efficient way to do it than the other way?

    G is the product name, H is the inventory id #. 12/6/24 are the different package configurations.




    Quote Originally Posted by DonkeyOte View Post
    I would add ...

    what data type resides in H (number, text) ...
    what do you want to return if it's an error...
    what version of Excel are you running...

    Assuming for ex. you're pre 2007... H contains numbers... for error return 0...

    Please Login or Register  to view this content.
    (if you wanted blank rather than 0 you could use a Custom Format to set 0 to null)

    Ditto other comments re: INT
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1