+ Reply to Thread
Results 1 to 8 of 8

multiple row and column criteria lookup - may be INDEX fn with row and column MATCH

  1. #1
    Registered User
    Join Date
    07-26-2015
    Location
    Hyderabad
    MS-Off Ver
    2010
    Posts
    3

    multiple row and column criteria lookup - may be INDEX fn with row and column MATCH

    Am trying to lookup a value that matches multiple rows and column criteria. Eg:


    January February March April
    Leather Black Big Bag 100 200 300 400
    Leather Red Small Bag 200 300 400 500
    Leather Green Big Bag 300 400 500 600
    Leather Yellow Small Bag 400 500 600 700
    Cotton Black Big Bag 500 600 700 800
    Cotton Red Small Bag 600 700 800 900
    Cotton Green Big Bag 700 800 900 1000
    Cotton Yellow Small Bag 800 900 1000 1100

    Lookup value for the following combination:

    Cotton Green Big Bag January
    Cotton Red Small Bag March
    Leather Yellow Small Bag February

    Could you please help write a formula that would lookup the value for the above combination.

    Thanks much in advance
    Attached Files Attached Files

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: multiple row and column criteria lookup - may be INDEX fn with row and column MATCH

    Enter this formula in F13 and copy down

    =INDEX($E$4:$H$11,MATCH(B13&C13&D13,INDEX(B$4:B$11&C$4:C$11&D$4:D$11,0),0),MATCH(E13,E$3:H$3,0))
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: multiple row and column criteria lookup - may be INDEX fn with row and column MATCH

    Try this...

    Entered in F13 and copied down:

    =SUMIFS(INDEX(E$4:H$11,0,MATCH(E13,E$3:H$3,0)),B$4:B$11,B13,C$4:C$11,C13,D$4:D$11,D13)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Registered User
    Join Date
    07-26-2015
    Location
    Hyderabad
    MS-Off Ver
    2010
    Posts
    3

    Re: multiple row and column criteria lookup - may be INDEX fn with row and column MATCH

    Dear Alkey,

    It worked perfectly.....Thanks so much for your super quick help.

    Really appreciate it.

    Regards...

  5. #5
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: multiple row and column criteria lookup - may be INDEX fn with row and column MATCH

    Range concatenation is inefficient.

    If you can do it without range concatenation then you should try some other method.

  6. #6
    Registered User
    Join Date
    07-26-2015
    Location
    Hyderabad
    MS-Off Ver
    2010
    Posts
    3

    Re: multiple row and column criteria lookup - may be INDEX fn with row and column MATCH

    Got you ...Thanks much Tony for your valuable advise the formula

    appreciate your quick response.

  7. #7
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: multiple row and column criteria lookup - may be INDEX fn with row and column MATCH

    You're welcome. Thanks for the feedback!


    If your question has been solved please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

  8. #8
    Valued Forum Contributor
    Join Date
    12-09-2014
    Location
    Trakai, Lithuania
    MS-Off Ver
    2010
    Posts
    517

    Re: multiple row and column criteria lookup - may be INDEX fn with row and column MATCH

    Formula: copy to clipboard
    Please Login or Register  to view this content.

+ 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. [SOLVED] Return Multiple values from a column with index and match, and search criteria
    By marcusduton in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 02-15-2015, 09:59 PM
  2. [SOLVED] Index/Match Formula - multiple criteria in one column
    By davemon in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 12-19-2014, 03:00 PM
  3. Multiple column lookup using index match
    By adaws in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-22-2013, 11:43 PM
  4. Replies: 7
    Last Post: 08-04-2013, 03:41 PM
  5. Replies: 3
    Last Post: 06-15-2012, 04:19 PM
  6. Replies: 2
    Last Post: 01-27-2012, 01:32 PM
  7. [SOLVED] Vlookup or index/match with multiple column and criteria?
    By xirx in forum Excel General
    Replies: 2
    Last Post: 07-22-2005, 08:05 AM

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