We're aware that some users are experiencing technical issues which the team are working to resolve. See the Community Noticeboard for more info. Thank you for your patience.
📨 Have you signed up to the Forum's new Email Digest yet? Get a selection of trending threads sent straight to your inbox daily, weekly or monthly!

Theory Behind Excel Code

Options
Cotta
Cotta Posts: 3,667 Forumite
edited 15 September 2016 at 10:46PM in Techie Stuff
Hi,

I know some are cross with me for posting Excel queries on here and I really don't want to annoy anyone. However I am trying to learn the theory behind a few things and I would appreciate if anyone is willing to help me with the "true" segment of the following "if" statement?

=IF(I30="n/a",VLOOKUP(A30,'test_tab'!A:IX,MATCH(VLOOKUP(VLOOKUP(A30,'test_tab'!A:IX,MATCH('tab1'!H$29,'EXT60'!$1:$1,0),0),'Department Lookups'!D:F,3,0),'test_tab'!$1:$1,0),0)

Step one is easy - if cell I30 is equal to the value "n/a" then perform a vlookup on cell A30 of the "test tab".

However after this I am a little confused as to what statement relates to what vlookups and I would really appreciate for someone to break this down for me.

I am genuinely grateful for all the help I do receive.

Comments

  • bod1467
    bod1467 Posts: 15,214 Forumite
    PLEASE!!!!

    Break EACH formula segment into its own cell. DON'T try to multi-nest formulas if you don't know what you're doing!
  • You'll get a lot more power out of Excel if you go and learn some VBA rather than try to nest formulas to death.

    One very useful skill is being able to write your own embedded functions, rather than macros
  • Chino
    Chino Posts: 2,031 Forumite
    Part of the Furniture 1,000 Posts Name Dropper
    =IF(I30="n/a",
        VLOOKUP(A30,
                'test_tab'!A:IX,
                MATCH(
                      VLOOKUP(
                              VLOOK UP(
                                       A30,
                                       'test_tab'!A:IX,
                                       MATCH(
                                             'tab1'!H$29,
                                             'EXT60'!$ 1:$1,
                                             0
                                       ),
                                       0
                              ),
                              'Department Lookups'!D:F,
                              3,
                              0
                      ),
                      'test_tab'!$1:$1,
                      0
                ),
        0
     )
    
  • AndyPix
    AndyPix Posts: 4,847 Forumite
    Fifth Anniversary 1,000 Posts Name Dropper Photogenic
    Cotta, please , why arent you posting on the specialist forum to get answers from specialists ??


    I really dont understand this


    http://www.mrexcel.com/forum/excel-questions/


    You will get better, specialised advice, why are you ignoring this suggestion ?
  • esuhl
    esuhl Posts: 9,409 Forumite
    Part of the Furniture 1,000 Posts Name Dropper
    You need to clearly break down the formula into its component parts, so that you can clearly see each parameter for each function -- a bit like Chino has done. It should then be easy(ish) to work out what the formula does.
  • bod1467
    bod1467 Posts: 15,214 Forumite
    Which is precisely why we've recommended putting each part of the formula in its own cell. :)
  • I think I feel a yawn coming on........:eek:

    Awfully to be going found in circles.

    Beats me why advice is requested and then not followed without explaining why...........
  • Le_Kirk
    Le_Kirk Posts: 24,511 Forumite
    Part of the Furniture 10,000 Posts Photogenic Name Dropper
    A shame the OP doesn't do what your user name suggests! ^^^^
This discussion has been closed.
Meet your Ambassadors

🚀 Getting Started

Hi new member!

Our Getting Started Guide will help you get the most out of the Forum

Categories

  • All Categories
  • 350.9K Banking & Borrowing
  • 253.1K Reduce Debt & Boost Income
  • 453.5K Spending & Discounts
  • 243.9K Work, Benefits & Business
  • 598.7K Mortgages, Homes & Bills
  • 176.9K Life & Family
  • 257.1K Travel & Transport
  • 1.5M Hobbies & Leisure
  • 16.1K Discuss & Feedback
  • 37.6K Read-Only Boards

Is this how you want to be seen?

We see you are using a default avatar. It takes only a few seconds to pick a picture.