Help using Index/Match for multiple rows with same value

Hi there,

I am looking to create a summary tab within a workbook.

the data tab I wish to summarise has multiple rows with varying descriptions, but these have been mapped to standardised descriptions in another column.

These standardised descriptions, for example 'Turnover', 'Cost of Sales' etc, occur many times in the column, and thus are an issue for match formulas which can only pick up 1 instance.

There are also multiple columns e.g 'Company 1' 'Company2' etc.

I wish to have a summary tab, where when you change the name from company 1 to company 2 (I created list through data validation), it populates the appropriate cells, e.g it will pull through the turnover relating to company 2 when company 2 is selected.

The method I have previously tried is as follows:

=INDEX(Sheet2!$A$3:$E$8,MATCH(A3,Sheet2!A3:A8,0),Sheet1!C1)

as an explanation - the index array is the data set in sheet 2, the rows to lookup is a match formula, matching 'turnover' to the column in sheet 2 where all the turnovers are mapped, and the column ref in the index formula is linked to sheet 1 c1. Sheet 1 c1 is a match formula, looking up 'company 1' within the data sheet, and returning the column reference.

The formula picks up the correct reference from the right company, however the problem I have is match only returns the first value in the data set, when there are for example 10 instances of turnover.

How do you sum all of company 1, or 2 etc without just picking up 1 row? I have tried to play around with INDIRECT formulas without success.

I'd appreciate any help on this.

many thanks,
Alex

Hello
Can you please attach a sample of your workbook so that we can get a better understanding of your problem...
You can do so by pressing the Go advanced button and then clicking on the paper clip icon...
http://www.excelforum.com/members/da...ch-a-file.html

Originally Posted by sourabhg98

Hello
Can you please attach a sample of your workbook so that we can get a better understanding of your problem...
You can do so by pressing the Go advanced button and then clicking on the paper clip icon...
http://www.excelforum.com/members/da...ch-a-file.html

Please find attached a very basic version of what I mean.

The list in Sheet 1 B1 can be changed, and the sum in B3 should be the total turnover for that particular company, for example 888 for company 2.

As you can see it returns 112, the first line matched to turnover.

I hope this helps.

Regards,
Alex

Originally Posted by sktneer

Try this.....
On Sheet1
in B3

Please Login or Register  to view this content.

and copy down.

Is this what you are trying to achieve?

This formula still only returns the first line of turnover, I need it to sum all instances of turnover for that company.

The issue is with the match formula in the rows I think

Originally Posted by sktneer

Try this.....

In B3

Please Login or Register  to view this content.

and copy down.

thats perfect thanks!

Hey I got your problem
Sorry for the late reply
Check out this one in B3
=SUMPRODUCT((Sheet2!C1:E1=B1)*(Sheet2!C3:E5))
Hope this helps..
If it works you can thank me by pressing the add reputation button below..
Regards