# Using Max, and looking up adjacent variables

#### johnburgin

##### New Member
Hi,

I have become quite proficient using sumproduct recently, but now I have a new challenge.

CLIENT GROUP; CHARGE; INCREASE COST; ORIGINAL CHARGE

What I need to do is find the maximum increase by CLIENT GROUP. I have done this using an array formula (in spite of my irrational fear of them) as follows:

{=MAX(IF(Data!D8:D914="Dementia",Data!Y8:Y914))}

And this works. What I need to be able to do is find values from columns either side of this specific answer. That is to say, the maximum value for this client may be the same for other client groups, so I need to find the ORIGINAL CHARGE and CHARGE relating to the answer of this search.

This I have no idea how to do. I have been reading up on using INDEX, but do not know how to build INDEX into my above formula. Remember, I have an irrational fear of array formulas, and get easily confused by syntax when a single formula has many, many functions

Many Thanks,

John

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
To return the value in column E try:

=INDEX(Data!E8:E914,MATCH(MAX(INDEX((Data!D8:D914="Dementia")*Data!Y8:Y914,)),INDEX((Data!D8:D914="Dementia")*Data!Y8:Y914,),FALSE))

It's not an array formula.

Excellent - many thanks! Now I need to get my head around how that works

johnburgin,

Perhaps try this. I have assumed that your formula is not in Data sheet?

Data....

Excel 2007
DEFGHIJKLMNOPQRSTUVWXYZ
7ClientChrgIncOrig
8n1814
9Dementia2513
10n3412
11n4711
12Dementia52610
13n679
14Dementia7228
15
Data

Results......

Excel 2007
ABC
1ChargeMax IncreaseOrig Charge
252610
Sheet1
Cell Formulas
RangeFormula
A2=INDEX(Data!X8:X914,SUMPRODUCT((Data!D8:D914="Dementia")*(Data!Y8:Y914=B2)*(ROW(Data!D8:D914)-7)),0)
C2=INDEX(Data!Z8:Z914,SUMPRODUCT((Data!D8:D914="Dementia")*(Data!Y8:Y914=B2)*(ROW(Data!D8:D914)-7)),0)
B2{=MAX(IF(Data!D8:D14="Dementia",Data!Y8:Y14))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Hope that helps.

Hi Tony,

That's interesting. I've not learnt much about how ROW works, but how is it working within the context of this sumproduct function here?

many Thanks,

John

Ah, I get it! Using sumproduct to find the row number, for the index! Brilliant. Thanks!

JB

Replies
6
Views
275
Replies
8
Views
569
Replies
6
Views
522
Replies
12
Views
638
Replies
9
Views
722

1,203,455
Messages
6,055,541
Members
444,794
Latest member
HSAL

### 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.

### Which adblocker are you using?

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

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