CODEWITHSUNDEEP
excelmax
Andy
Andy

Reputation: 71

Excel - Finding Max value in a column

I have an Summary sheet set up data set up as follows-

         Cat A  Cat B  Cat C  Cat D  
Name 1       0      0      0      0  
Name 2       2      3      2      2  
Name 3       2      2      2      2  
Name 4       3      2      2      3  
Name 5       2      3      2      3

I also then have separate tabs for each of Name1 through to Name 5.

The summary sheet contains the maximum values for each category from each tab. So the Cell at Cat A Name 1 should show the maximum value on Sheet(Name1) in the Cat A column.

So far so good. However each tab may not contain the same categories, so therefore I would like teh summary sheet to check the maximum value in each column by doing a search on the Cat name.

So far I have this-

=MATCH(Overview!S$1,Name1!$C$1:$V$1,0)

Which returns the column number with the right Category, in this case 13. So I can find the right column. What I am struggling with is to now find the maximum value in the column.

Can anyone help?

Thanks

Upvotes: 2

Views: 5751

Answers (2)

Doug Glancy
Doug Glancy

Reputation: 27478

IAssuming your search range goes to row 1000:

=MAX(INDEX(Name1!$C$2:$V$1000,0,MATCH(Overview!S$1,Name1!$C$1:$V$1,0)))

The 0 Row argument in Index means to select the entire column.

Upvotes: 3

McKay
McKay

Reputation: 12604

The Offset function is your key here.

After you've got the value from the match, you can pass it to the offset to get the correct column.

So, for example, you probably want something like:

=Max(Name1!$C1:$C2000)

But you don't know whether you should use the C column or the D column or whatever, in this case, it was 13, so is that the P column? (c=3, the match was 13 so 3+13 = 16 = P?), so I think you want something like this:

=Max(Offset(Name1!$C$1:$C$2000, 0, [result of your match expression] - 1))

Here's an example of what I think you want in GoogleDocs:

https://docs.google.com/spreadsheet/ccc?key=0Ai45AJPc2AWMdGRlZXNIdlZBaHJxc01qVlJWa1N1WXc

Upvotes: 1

Related Questions