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

  • What if customer in row 9 bought 100 P1 AND 100 P5? – Raystafarian Feb 12 '14 at 14:02
  • either can be considered as the customer's max (or p1 since it comes first). – Annie Feb 12 '14 at 14:07
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..

  • @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 '14 at 10:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.