+ Reply to Thread
Results 1 to 7 of 7

Index and Match for 4 Criteria - My 2 attempts are not working

  1. #1
    Registered User
    Join Date
    12-01-2008
    Location
    London
    Posts
    25

    Index and Match for 4 Criteria - My 2 attempts are not working

    Hello Forum.
    Would really appreciate some help please.
    I require some assistance to index & match for 4 criteria. I can get the formula to work for 3 criteria - but I need the extra criteria to stipulate for Gross or Net values.

    Many thanks - Have a look and see if you can crack it. I have left my attempts in so you can see what I have tried.
    (note: do not want to use a pivot on this occasion). Assume index/match is the best formula for this problem.
    denise
    Attached Files Attached Files
    Last edited by denise001; 08-25-2013 at 08:24 AM.

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Index and Match for 4 Criteria - My 2 attempts are not working

    in n3
    =INDEX($D$1:$H$70,MATCH($K$1&$L3&N$2,INDEX($A$1:$A$70&$C$1:$C$70&$B$1:$B$70,0),0),MATCH($M3,$D$1:$H$1,0))
    filled across to o3 then down

    in u3
    =INDEX($D$1:$H$70,MATCH($R$1&$S3&U$2,INDEX($A$1:$A$70&$C$1:$C$70&$B$1:$B$70,0),0),MATCH($T3,$D$1:$H$1,0))
    filled across to v3 then down
    "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

  3. #3
    Registered User
    Join Date
    12-01-2008
    Location
    London
    Posts
    25

    Re: Index and Match for 4 Criteria - My 2 attempts are not working

    Martin - It works! Thank you soooooo much - and looking at your formula, I know where I went wrong. Will set to solved. Once again - Thank you - what a star. Best regards.denise

  4. #4
    Registered User
    Join Date
    12-01-2008
    Location
    London
    Posts
    25

    Re: Index and Match for 4 Criteria - My 2 attempts are not working

    May I re-open this case? I have the same problem, but this time my formula for a 4 criteria "lookup" does not work. Spreadsheet enclosed with my efforts. Just cannot understand why this will not get the data.
    Would really love another helping hand please.
    Many thanks
    denise
    Attached Files Attached Files

  5. #5
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Index and Match for 4 Criteria - My 2 attempts are not working

    that should be
    =INDEX($E$1:$L$141,MATCH($O$1&$O$3&$P$3&Q3,INDEX($B$1:$B$141&$A$1:$A$141&$C$1:$C$141&$D$1:$D$141,0),0),MATCH(R$2,$E$1:$L$1,0))

  6. #6
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Index and Match for 4 Criteria - My 2 attempts are not working

    forum double posting again

  7. #7
    Registered User
    Join Date
    12-01-2008
    Location
    London
    Posts
    25

    Re: Index and Match for 4 Criteria - My 2 attempts are not working

    Thank Martin. Apologies - I have just solved it - Would you like me to post the solution with an explanation of where I went wrong?
    denise

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Correct/Working (Index,Match) formula not working between cells
    By barnerd in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 02-11-2014, 01:20 PM
  2. [SOLVED] Index Match Multiple Criteria not working
    By garciapliz in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-22-2013, 12:07 PM
  3. INDEX MATCH MATCH working great and then failing on me.
    By HeikEve in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-25-2013, 01:40 PM
  4. Replies: 2
    Last Post: 05-24-2013, 09:32 AM
  5. [SOLVED] INDEX/MATCH not working for multiple criteria
    By lukesanborn in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-27-2012, 09:20 PM

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