Nested Index, Match with MAX

[jackson_hollon]

Hello everyone,

Thanks for looking at my post. I need a formula I believe is using Index, Match with Max.

I have attached a test page here. I need a formula on column D. If column C is not US, then column will be the same as column C. But if column C is US, then:

For each customer in column A, based on the total combined max invoices amount in column B of the country in column C, the column D will show the highest invoice amount of that country.

Please be aware that I have sort the column A for easier understanding . But in real the column A will not be sorted.

Thanks for the help.

[JohnTopley]

On your file you show COUNTRY with highest amount not the Max amount itself as per your description. Which is it ?

And it would be better to post a file with the real organisation of data to avoid the potential of providing a wrong or unsuitable solution.

[jackson_hollon]

John,

It is the combined invoices total of the highest amount between the counties other than US.

[AliGW]

In D2 copied down:

=IF(C2="US",INDEX($C$2:$C$27,MATCH(MAXIFS($B$2:$B$27,$A$2:$A$27,A2,$C$2:$C$27,"<>US"),$B$2:$B$27,0)),C2)

[jackson_hollon]

AliGW,

That is it. This is exactly what I need.

Thanks for the help.

[AliGW]

You may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.