2

i have a list of customers rows 1-500 column B, with their purchases showing in columns u-AB and based on the maximum purchases done under a specific column (u-ab), the customer will be tagged as such. example:

if customer in row 10 buys: 33 of P1, 20 of P2, 44 of P4, none of P3, p5-p8... we tag him to be a "P4" customer since that is the product he has purchased the most during the year.

i had this formula...

=HLOOKUP(MAX(U9:AB9),U9:AB313,COUNTA($A$1:$A$306)-ROW(A9)+1,0)

customer of row 9, maximum of U9-ab9. due to Hlookup must copy titles onto the last row too.

this formula is inconsistent, after some row, it just gives "0". I need consistent report!

the logic is: "see the max under a column, and return the column title in this cell".

i am sure max, index match , will do the trick... but don't know HOW....

HELP!!! thanks in advance

Improve this question
2
  • What if customer in row 9 bought 100 P1 AND 100 P5?
    – Raystafarian
    Feb 12, 2014 at 14:02
  • either can be considered as the customer's max (or p1 since it comes first).
    – Annie
    Feb 12, 2014 at 14:07

1 Answer 1

Reset to default
1

This assumes the headers are in row 1.

=INDEX($U$1:$AB$1,1,(MATCH(MAX(U9:AB9),U9:AB9,0))

This will work for the first match on the maximum value -

=INDIRECT(ADDRESS(1,(MATCH(MAX(U9:AB9),U9:AB9,0)))

(Assuming column titles are in Row 1)

Indirect is a volatile function, so there might be a better way..

Improve this answer
1
  • @Annie if this solution worked for you, click the green check mark to the left of the answer so that this question is marked as solved, in case someone else is looking for a similar answer or someone is going through questions looking to provide solutions.
    – Raystafarian
    Feb 16, 2014 at 10:36

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .