INDEX & MATCH Function

[excellicious]

It's me again and seeking your expertise. Please review attachment.

My first tab consists of data for various products from various Districts and it also has a grand total for all districts, which is the "System" total. Cells that have the System data are not always "absolute".

For my second tab, I have a summary table that I would like these "System" total to transfer too by using the Index & Match function but I'm not sure why it doesn't work.

=INDEX(DATA!A2:D119,MATCH(A3,DATA!A2:A119,1),MATCH(DATA!B113,DATA!B2:B119,3))

Gracias. Excellicious

[psumvp]

There are a couple of problems with your formula... First the index function is (Array, Row, Column). The way you have the formula set up you are putting your results in row then column, but in actuality you want Row + Row, and a 1 for column. The second problem is that you are just trying to match the subcategory to all of the B column and the match will likely return the wrong entry.

The formula below matches the a3 cell then adds to it the match for the sub category. But it uses the indirect function to define an array of data starting with that row and ending below the button of the table. With match and third argument of 0, you will get the first result found.



=INDEX(DATA!C:C,MATCH($A$3,DATA!$A$2:$A$119,1)+MATCH($B3,INDIRECT(CONCATENATE("Data!B",MATCH($A$3,DATA!$A$2:$A$119,0)+1,":B700")),0),1)

[excellicious]

Thank you for your time and efforts.

I tested your formula and it gave me a "#NAME?"?? Back to the drawing board.


Excellicious.

[psumvp]

for some reason the copy and paste added an extra space in MATCH and in DATA. If you remove them, it works.

[excellicious]

Yeah..It works so cheers to you!!! Thank you for kind assistance. Now I can go to bed