+ Reply to Thread
Results 1 to 6 of 6

Match Index? to pull data according to multiple criteria

  1. #1
    Registered User
    Join Date
    03-04-2010
    Location
    Wisconsin
    MS-Off Ver
    Excel 2007, Excel 2010, Excel 2003
    Posts
    87

    Red face Match Index? to pull data according to multiple criteria

    Hi all, Been a while since I have asked for help and unfortuately in the course of time I am now a little too rusty with regards to Match/Index. I need to do the following
    1.I want to pull data from the "FY10 Actual (USD) table or whatever table is listed in Row 1
    2.I then want only the value in which the line in the "Budget" matches the value in Row 2 of the Analysis page. eg "Total EE Expenses" and for the Locations listed in Col A of the Analysis sheet and in the row of the Budget sheet. I am hoping my attached workbook will make it easier to explain.
    Attached Files Attached Files
    Last edited by dawnmau; 01-05-2011 at 03:14 PM. Reason: Solved!!
    Dawn - Guru in Training

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Match Index? to pull data according to multiple criteria

    Based purely on the setup of your sample file:

    Please Login or Register  to view this content.
    note: you can copy above to F4:G6 also but must remember to add back the subtractions.

    Above is as I say based on sample where each table is identical in terms of rows and locations are listed per their column order.

  3. #3
    Registered User
    Join Date
    03-04-2010
    Location
    Wisconsin
    MS-Off Ver
    Excel 2007, Excel 2010, Excel 2003
    Posts
    87

    Re: Match Index? to pull data according to multiple criteria

    Hey DonkyOte! Thanks for the fast reply! The formula worked on the sample file, but when I tried to apply to my actual file, it doesn't appear to work. 2 questions for you...1) what does the -1 do? and 2) does it make a difference if the "Budget" worksheet is actually part of a separate file? (I pointed it to the separate workbook) Also if I follow your format, what can cause the formulas to not work? ie does the format of the cells impact whether something matches or not?

    Dawn

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Match Index? to pull data according to multiple criteria

    The formula references Budget!C:E given this is where the result is coming from.

    The first MATCH finds the "header" in Col A and returns the row position

    The second MATCH finds the COA entry in Col A and returns the row position - we assumed (per sample) that all tables were identical in their layout
    (ie Total Expenses is for sake of ex. the 6th row in each table [made up number])

    If we add MATCH 1 and MATCH 2 we need to deduct 1 to avoid double counting the first row in the table.

    The result of MATCH 1 + MATCH 2 - 1 is the ROW number in which our Table:COA appears within the Range

    The ROWS element is used to determine the COLUMN index we intend to retrieve from our range - so the first row retrieves column 1 (C), the 2nd row retrieves column 2 (D) etc

    If you're getting errors in your file - I suspect it will be down to the fact that the various tables are not identical, however, for us to check we need to see the real thing... trim down and dummy $ values etc to avoid confidentiality issues.
    Last edited by DonkeyOte; 01-05-2011 at 03:24 PM. Reason: typo

  5. #5
    Registered User
    Join Date
    03-04-2010
    Location
    Wisconsin
    MS-Off Ver
    Excel 2007, Excel 2010, Excel 2003
    Posts
    87

    Re: Match Index? to pull data according to multiple criteria

    Ok so I am attaching the "Analysis" File and the"Budget" File which will be a separate Workbook and in the same format of what I will be using in the actual files.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    03-04-2010
    Location
    Wisconsin
    MS-Off Ver
    Excel 2007, Excel 2010, Excel 2003
    Posts
    87

    Re: Match Index? to pull data according to multiple criteria

    Ok, I got it to work by restricting the areas to look at (instead of using the whole columns $A:$A, I typed $A##:$A###, with ## being the specific row number) You have been an invaluable help and will award you extra gold stars!!

    Thanks a bunch!!
    Dawn

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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