CODEWITHSUNDEEP
excelexcel-2007excel-formula
Josef
Josef

Reputation: 585

Get max value column from looked up row/range

After many unsuccessful tries I need your help with a formula.

I am trying to get the max value in a range of cells that should be looked up somehow.

I have a sales table for each item number which contains the amount of sales in each of four contries

    SE   DK  FI  NO
#1  10%  20% 70% 0%
#2  20%  20% 50% 10%

So, this table is called "Salessplit".

In another sheet I have rows with item numbers where I would like to return the highest selling country for that item.

#1 Look up #1 from Salessplit and return "FI"(that has the highest sales)
#2 Look up #2 from Salessplit and return "FI" here as well

Any idea how to manage this?

Upvotes: 1

Views: 1461

Answers (2)

nutsch
nutsch

Reputation: 5962

With the same assumptions, but with a slightly different approach, using offset instead of a couple indexes.

=INDEX(Sheet1!A1:E1,,MATCH(MAX(OFFSET(Sheet1!A1:E1,MATCH(Sheet1!A2,Sheet1!A:A,0),0,1,)),OFFSET(Sheet1!A1:E1,MATCH(Sheet1!A2,Sheet1!A:A,0),0,1,),0))

Upvotes: 1

barry houdini
barry houdini

Reputation: 46331

Assume your table is in Sheet1!A1:E10 with headers in row 1 and item numbers in column A then try this formula in sheet2!B2 assuming that the item number is in Sheet2!A2

=INDEX(Sheet1!B$1:E$1,MATCH(MAX(INDEX(Sheet1!B$2:E$10,MATCH(A2,Sheet1!A$2:A$10,0),0)),INDEX(Sheet1!B$2:E$10,MATCH(A2,Sheet1!A$2:A$10,0),0),0))

Upvotes: 3

Related Questions