Formula for finding the Location from where Maximum Amount had been derived

[rush2rajen]

Hi Excel Gurus,

Please find the attached file. I want to find out the Location from where MAXIMUM Amount had been derived.
Details given in file.

I Want to give a REMARKS as " NOT APPROVED" or can do CONDITIONAL FORMATTING with RED BACKGROUND for the example given

And the same is applicable to others also that they need to sell maximum in their location only and not in other locations.


Regards
Raj

 

[Somendra Misra]

Hi Raj,

See this file, I had not considered Duplicate cases.

Regards,

 

[rush2rajen]

Misraji,

Thanks a lot. It completes my data

Regds
Raj

 

[Somendra Misra]

Welcome back Raj!!

Regards,

 

[rush2rajen]

Misraji,

One request if you can keep.
I would request you to kindly elaborate the formula and make me understand

Thanks
Raj

 

[Somendra Misra]

@rush2rajen

So here the formula:

=IF(INDEX($D$2:$M$2,,MATCH(MAX($D3:$M3),$D3:$M3,0))=$C3,"OK","Not Approved")

The Logical Portion marked in Red, is checking the location allotted in column C to a sales person say C3 whether it is equal to the location with max sale. So index is getting the location of max sales. If they are equal then OK other wise Not Ok.

INDEX will return the value from the column headers i.e. locations by matching max sales for that rep with the sales volumes.

If you still have a problem than write back.

Regards,

 

[Luke M]

Just for fun, here's another approach you could use. Slightly less calculations. In N3:
=IF(LOOKUP(C3,D$2:M$2,D3:M3)=MAX(D3:M3),"OK","Not Approved")

Looks up the value for specified location, and checks if it matches the max.

 

[John Jairo V]

Hi @rush2rajen, (and @Somendra Misra, @Luke M)

You can "short" a little more the formula like this (In N3):
=IF(LOOKUP(C3,D$2:M3)=MAX(D3:M3),"OK","Not Approved")
Blessings!

 

[Luke M]

Lol, nice John.
I did think of that, but got worried that if we dragged down to far, we might get more rows than columns in the LOOKUP array. I guess it depends on how many rows you need the formula for.

 

[John Jairo V]

Yep @Luke M. You're Right... lookup function work well in that particular case, 'cuz the columns are more than the rows.

In all cases, we can "short" a little bit if we use:
=IF(LOOKUP(C3,D$2:M$2,D3)=MAX(D3:M3),"OK","Not Approved")
The Third argument take the lenght of the range in the 2nd argument ('cuz it is horizontal). Blessings!

 

[rush2rajen]

Thanks Misraji Understood.

Thanks a lote John and Luke

Regds
Raj