+ Reply to Thread
Results 1 to 9 of 9

Excel Formula gives error

  1. 12-20-2011, 02:15 AM #1
    gpatel
    gpatel is offline
    Registered User
    Join Date
    12-20-2011
    Location
    Rajkot, India
    MS-Off Ver
    Excel 2010
    Posts
    12

    Exclamation Excel Formula gives error

    =IF(ISBLANK(E16),"PINCODE REQUIRED", IF((OR(LEFT(E16,2)="67",LEFT(E16,2)="68",LEFT(E16,2)="69")),(IF(ISBLANK(A22),"",IF(A22="GOLD COIN -500",Data!X2,IF(A22="GOLD BOND -02G",Data!X3,IF(A22="GOLD BOND -04G",Data!X4,IF(A22="GOLD CAST",Data!X5,IF(A22="GOLD MINE -10G",Data!X6,IF(A22="GOLD MINE -20G",Data!X7,IF(A22="GOLD MINE -30G",Data!X8))))))))),(IF(ISBLANK(A22),"",IF(A22="GOLD COIN -500",Data!Y2,IF(A22="GOLD BOND -02G",Data!Y3,IF(A22="GOLD BOND -04G",Data!Y4,IF(A22="GOLD CAST",Data!Y5,IF(A22="GOLD MINE -10G",Data!Y6,IF(A22="GOLD MINE -20G",Data!Y7,IF(A22="GOLD MINE -30G",Data!Y8)))))))))))

    It gives error that it uses more level of nesting then supported I am using Office 2007.

    Thanks in advance.

    Gpatel
    Reply With QuoteRegister To Reply
  2. 12-20-2011, 04:02 AM #2
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Excel Formula gives error

    This is what LOOKUP formulas are for. It appears you have values in Data!X2:X8 and Data!Y2:Y8 that you want to draw from. A proper lookup table would have your values to lookup in column W:


    Please Login or Register  to view this content.

    With that layout, your formula simplifies down to:

    =IF(ISBLANK(E16), "PINCODE REQUIRED", IF(ISBLANK(A22), "", IF(OR(LEFT(E16,2)={"67","68","69"}), VLOOKUP(A22, Data!$W$2:$X$8, 2, 0), VLOOKUP(A22, Data!$W$2:$Y$8, 3, 0))))
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)
    Reply With QuoteRegister To Reply
  3. 12-20-2011, 07:48 AM #3
    gpatel
    gpatel is offline
    Registered User
    Join Date
    12-20-2011
    Location
    Rajkot, India
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Excel Formula gives error

    I tried your solution and even tried modifying( Because my data columns are as below) but no result 

    Please Login or Register  to view this content.
    Can you please help.
    Reply With QuoteRegister To Reply
  4. 12-20-2011, 09:02 AM #4
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Excel Formula gives error

    post a workbook!
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula
    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC
    Reply With QuoteRegister To Reply
  5. 12-20-2011, 09:13 AM #5
    gpatel
    gpatel is offline
    Registered User
    Join Date
    12-20-2011
    Location
    Rajkot, India
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Excel Formula gives error

    Please find attached the File, The formula will go in O26
    Attached Files Attached Files
    Last edited by gpatel; 12-20-2011 at 09:16 AM.
    Reply With QuoteRegister To Reply
  6. 12-20-2011, 10:08 AM #6
    Sadath31
    Sadath31 is offline
    Valued Forum Contributor Sadath31's Avatar
    Join Date
    03-02-2011
    Location
    Dammam, Saudi Arabia
    MS-Off Ver
    Office 365
    Posts
    452

    Re: Excel Formula gives error

    Hi
    try this
    =IF(ISBLANK(E16),"PINCODE REQUIRED",IF(ISBLANK(A22),"",VLOOKUP(A22,Data!$A$2:$Y$8,NOT((LEFT(E16,2)={"67","68","69"}))+24,0)))
    Reply With QuoteRegister To Reply
  7. 12-20-2011, 11:41 AM #7
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Excel Formula gives error

    The original suggested formula adjusted:

    =IF(ISBLANK(E16), "PINCODE REQUIRED", IF(ISBLANK(A22), "", IF(OR(LEFT(E16,2)={"67","68","69"}), VLOOKUP(A22, Data!$A$2:$X$8, 24, 0), VLOOKUP(A22, Data!$A$2:$Y$8, 25, 0))))
    Reply With QuoteRegister To Reply
  8. 12-21-2011, 01:02 AM #8
    gpatel
    gpatel is offline
    Registered User
    Join Date
    12-20-2011
    Location
    Rajkot, India
    MS-Off Ver
    Excel 2010
    Posts
    12

    Smile Re: Excel Formula gives error

    Thank you for your help and formula.

    It really helped and I also understood the Vlookup function, Especially the Col_index_num
    Reply With QuoteRegister To Reply
  9. 12-21-2011, 05:29 AM #9
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Excel Formula gives error

    If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.
    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