INDEX & MATCH WITH MAX Value

[Flora1]

Hello everyone,

I`m working on a spreadsheet and got stuck on the following: I have the following data: ID (column A), Country(column B) and Number of new clients(column C). What I need as a result is the country which has generated most new customers based on the ID.

I have written the following for getting the highest number but can`t seem to be able to add looking by ID in the formula and it functioning properly.


=INDEX(B2:B350,MATCH(MAX(C2:C350),C2:C350,0))

[tim201110]

=index(b2:b350,match(max(if(a2:a350=id,c2:c350)),c2:c350,)) CSE

[Flora1]

Thanks, maybe I didn`t quite explain this but there are several numeric IDs listed in A so the lookup needs to be based on two criteria: the respective ID and the country where it got most clients from.

I`ve tried replacing the a2:a350=id with a cell reference but no luck. Any ideas what to replace it with?

[JeteMc]

Hello Flora1 and Welcome to Excel Forum.
Be sure that after editing Tim's formula you activate by simultaneously pressing the Ctrl, Shift and Enter keys.
If that doesn't help then I would suggest uploading a sample spreadsheet that manually demonstrates your expected outcome.
To attach a sample workbook (not a picture or pasted copy) click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
Let us know if you have any questions.