Return column heading if a cell meets certain criteria in a row

[Usernew]

I have a table of over 2000 rows of ClientID's and the number of times they have used a particular service in each of 12 columns. I need to be able to see the column heading of the service that each Client has used most frequently over a certain period.

I have been able to obtain the MAX at the end of each row, but I need to be able to establish the percentage of Clients who use particular services most frequently.

E.g.

Client Svc1 Svc2 Svc3 Svc4 Svc5 Svc6 Svc7 Max SvcName
10000 12 23 4 7 0 5 13 23 ????????
10510 0 45 0 0 6 0 1 45 ????????

I have considered VLOOKUP but that doesn't work. Any suggestions would be appreciated.

 

[cml19722000]

I have a table of over 2000 rows of ClientID's and the number of times they have used a particular service in each of 12 columns. I need to be able to see the column heading of the service that each Client has used most frequently over a certain period.

I have been able to obtain the MAX at the end of each row, but I need to be able to establish the percentage of Clients who use particular services most frequently.

E.g.

Client Svc1 Svc2 Svc3 Svc4 Svc5 Svc6 Svc7 Max SvcName
10000 12 23 4 7 0 5 13 23 ????????
10510 0 45 0 0 6 0 1 45 ????????

I have considered VLOOKUP but that doesn't work. Any suggestions would be appreciated.

Try INDEX(MATCH Function...

=INDEX(B$1:H$1,MATCH(MAX(B$2:H$2),B$2:H$2,0))

p/s: assuming your data start at row-2 from column B to H

 

[Usernew]

Thanks, cml19722000!

I forgot to say that I was using Excel 2003 and I discovered that the INDEX function doesn't work in that version.

However, I tried it in Excel 2007 and it works perfectly. Deeply appreciated.
I wonder if there is any way to get the same result in Excel 2003?

 

[Aladin Akyurek]

Thanks, cml19722000!

I forgot to say that I was using Excel 2003 and I discovered that the INDEX function doesn't work in that version.

However, I tried it in Excel 2007 and it works perfectly. Deeply appreciated.
I wonder if there is any way to get the same result in Excel 2003?

Should work on all versions...

I2, just enter:

=MAX(B2:H2)

J2, just enter:

=INDEX($B$1:$H$1,MATCH(I2,B2:H2,0))

Note that this will pick out the service that corresponds to the first occurrence of the max value.

 

[Usernew]

Thanks a lot. It works a treat!

 

[Aladin Akyurek]

Thanks a lot. It works a treat!

Ypu are welcome. Thanks for providing feedback.