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.

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.

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

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.

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.

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