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