We'd like to remind Forumites to please avoid political debate on the Forum... Read More »
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
Posts: 3,667 Forumite
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.
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.
0
Comments
-
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!0 -
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 macros0 -
=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 )
0 -
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 ?0 -
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.0
-
Which is precisely why we've recommended putting each part of the formula in its own cell.0
-
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...........0 -
A shame the OP doesn't do what your user name suggests! ^^^^0
This discussion has been closed.
Confirm your email address to Create Threads and Reply

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