+ Reply to Thread
Results 1 to 4 of 4

Index & Match a table of data using multiple parameters

  1. #1
    Registered User
    Join Date
    06-10-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    1

    Index & Match a table of data using multiple parameters

    Hi,

    First time on this forum. I desperately need help with the following issue.
    Cannot figure out how to match parameters based on more than one cell on a data sheet and lookup the instersecting result in a table another sheet (Sheet2).

    There are 3 lookup parameters in this particular case. 2 cells cover a business name and business unit which need to match with first 2 rows of cells in the Sheet2!table followed by the corresponding business 'status' which can alter from month to month. There is 60 months of current and historical Business/Unit status in the table.

    The third parameter cell is a month/year date (e.g. Sep13) which can be manually altered and matches with the column headings in the Sheet2 table.

    I have provided an example.xlxs attachment which I hope makes more sense.
    I have a had a few goes with Index and Match but just cannot get a handle on this.

    Any help would be very grateful

    Thanks
    MJM
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Index & Match a table of data using multiple parameters

    Hi and welcome to the forum

    Try this ARRAY formula in B19...
    =INDEX($A$2:$F$13,MATCH($B$17&$B$18,$A$2:$A$13&$B$2:$B$13,0),MATCH($B$16,$A$2:$F$2))
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Index & Match a table of data using multiple parameters

    Another way to do this.....

    Please Login or Register  to view this content.
    This is an array formula so it needs to be confirmed with Ctrl+Shift+Enter
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  4. #4
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,360

    Re: Index & Match a table of data using multiple parameters

    Non Array :

    =INDEX(B3:F13,0,MATCH(B16,B2:F2)) INDEX(B3:F13,MATCH(B18,B3:B13),0)

    or

    =VLOOKUP(B17,A3:F13,MATCH(B16,B2:F2,0),TRUE)

    Azumi

+ 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] Index Match table using two columns of data
    By purdue7997 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-02-2013, 08:25 AM
  2. Data table based on user selection and index/match
    By pauldaddyadams in forum Excel General
    Replies: 0
    Last Post: 10-19-2012, 07:58 AM
  3. [SOLVED] Using LOOKUP, MATCH, INDEX to obtain cell value from data table
    By rfernandes in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 09-05-2012, 05:31 PM
  4. [SOLVED] Problem with INDEX, MATCH, and a function to count data from a table
    By Pentasyllabic in forum Excel General
    Replies: 10
    Last Post: 05-22-2012, 05:16 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