# Max(if) to return another columns value

#### confused in Frankfurt

##### Board Regular
Hi There,
I have a problem I need to not only return the highest value per sales rep but the name of the deal.

 Opportunity Name Opportunity Owner Unique Sales Price Arriva_Mittelstand Andrea Junge 130.000 Siemens-CIT-ARIS Design Server Denis Wachsmuth 17.600 Telefónica Alfabet Dirk Klimas 500.000 DB Systel_Terminalemulation Andrea Junge 800.000

<tbody>
</tbody>

<tbody>
</tbody>

The following formula returns the highest deal in the portfolio but how to get the name of the deal is the problem.
=MAXA(if('Pipeline SF '!H:H=Highlights!\$B60;'Pipeline SF '!AI:AI;0))

Regards
Sarah

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### Momentman

##### Well-known Member
Here's my set-up

Excel 2010
ABC
1Opportunity NameOpportunity OwnerUnique Sales Price
2Arriva_MittelstandAndrea Junge130
3Siemens-CIT-ARIS Design ServerDenis Wachsmuth17.6
4Telefnica AlfabetDirk Klimas500
5DB Systel_TerminalemulationAndrea Junge800
6
7OWNERNAMEMAXIMUM
8Andrea JungeDB Systel_Terminalemulation800
Sheet1
Cell Formulas
RangeFormula
B8{=INDEX(\$A\$2:\$A\$5,MATCH(2,(B2:B5=\$A\$8)+(C2:C5=\$C\$8),0))}
C8{=MAX(IF(B2:B5=A8,C2:C5))}
Press CTRL+SHIFT+ENTER to enter array formulas.

The "2" in the MATCH formula is based on the fact that if the two condition are met i.e NAME and Value, you have TRUE+TRUE which is 2 (something like that)

#### confused in Frankfurt

##### Board Regular
Hi what happens if the returned max value is available twice or more?

#### Momentman

##### Well-known Member
Can the maximum value appear more than once for each owner? e.g can you have Andrew Junge, 800 and on another line Andrew Junge, 800?

My formula is matching both the name and the max value for that specific name

#### Canapone

##### Active Member
Hi,

in the example: in C1:C1000 names of the deal

=INDEX('Pipeline SF '!C1:C1000;MATCH(MAXA(if('Pipeline SF '!H:H=Highlights!\$B60;'Pipeline SF '!AI1:AI1000;0))&Highlights!\$B60;'Pipeline SF '!AI1:AI1000&'Pipeline SF '!H1:H1000;0))

to be confirmed with control+shift+enter

You'll have for sure to adjust formula references.

I hope it helps

#### Momentman

##### Well-known Member
In the event there are multiple matches, you could use the array formula in E2 and drag downwards

Excel 2010
ABCDE
1Opportunity NameOpportunity OwnerUnique Sales PriceMATCHES
2Arriva_MittelstandAndrea Junge800Arriva_Mittelstand
3Siemens-CIT-ARIS Design ServerDenis Wachsmuth17.6DB Systel_Terminalemulation
4Telefnica AlfabetDirk Klimas500
5DB Systel_TerminalemulationAndrea Junge760
6Siemens-CIT-ARIS Design ServerDenis Wachsmuth17.6
7Telefnica AlfabetDirk Klimas500
8DB Systel_TerminalemulationAndrea Junge800
9
10
11OWNERMAXIMUM
12Andrea Junge800
Sheet1
Cell Formulas
RangeFormula
E2{=IFERROR(INDEX(\$A\$2:\$A\$8,SMALL(IF(\$B\$2:\$B\$8=\$A\$12,IF(\$C\$2:\$C\$8=\$B\$12,ROW(\$A\$2:\$A\$8)-ROW(\$A\$2)+1)),ROW(A1))),"")}
B12{=MAX(IF(B2:B8=A12,C2:C8))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Last edited:

#### vds1

##### Well-known Member
Alternatively you can have pivot table. Select column A , B , C and go to insert pivot table. Follow the instruction as per your needs. Remember that Pivot can be in existing worksheet.

1. Move Unique sales price to ∑ values
2. Move Opportunity Name to Row Labels
3. Under ∑ values you may see the count, so select the drop down | Value field settings | select sum
4. You will see row labels on the table, select drop down | Value Filters | Top 10 | select 1 instead of 10.

Replies
1
Views
441
Replies
0
Views
360
Replies
7
Views
460
Replies
1
Views
371

1,191,707
Messages
5,988,229
Members
440,139
Latest member
ngaicuong2017

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back