# Single formula required to fetch the name of the product that has max sales

##### New Member
Hi All

Please see snapshot below. It has sales data on cell no M13:X22 and Product name on L14:L22.

Now my main objective is to know the name of the product that has maximum sales during the year. Also with Index, Match and Max (using a supportive column) I am able to fetch the desired result. However need a single formula. Please help...

Many Thanks

DATA TABLE:

 L M N O P Q R S T U V W X Y 13 Product Name Jan-12 Feb-12 Mar-12 Apr-12 May-12 Jun-12 Jul-12 Aug-12 Sep-12 Oct-12 Nov-12 Dec-12 supportive Column 14 ABC CO 16919 30036 30169 30646 100000 42762 42631 40651 40495 45547 38098 43955 100000 15 PQRIN CO 2945 4535 5596 5806 2221 4051 3642 3324 5288 3372 3977 5784 5806 16 ASDDA 1894 5926 3039 4217 5133 876540 4056 1008 1389 2510 1277 1845 876540 17 WERTT 4868 2823 3141 3613 2658 4675 5835 1178 5034 2082 3808 5714 5835 18 QURDO 3181 1880 4581 2285 1063 1573 5958 2388 1387 1914 3114 3473 5958 19 Pentenal 4435 1485 1692 3309 5328 4919 3712 2444 3880 2641 3900 4130 5328 20 Centere 3282 1913 1567 2039 5312 1042 5587 2947 1905 1630 5774 1759 5774 21 Geneet 5531 3846 4522 3338 2693 2657 5094 3487 4238 2921 4376 5320 5531 22 Senet 5081 4247 4187 5000 1679 1698 3680 3188 2151 5606 2244 1931 5606 23 24 MAX Sales ASDDA =INDEX(L14:L22,MATCH(MAX(M14:X22),Y14:Y22,0))

<tbody>
</tbody>

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Try:

=INDEX(L14:L22,MATCH(MAX(INDEX(SUBTOTAL(4,OFFSET(M14:X14,ROW(L14:L22)-ROW(L14),0)),)),INDEX(SUBTOTAL(4,OFFSET(M14:X14,ROW(L14:L22)-ROW(L14),0)),),FALSE))

Many thanks Andrew, this is indeed helpful...

=INDEX(\$L\$14:\$L\$22,MAX((\$M\$14:\$X\$22=MAX(\$M\$14:\$X\$22))*ROW(\$M\$14:\$X\$22))-13)

confirmed with Ctrl+Shift+Enter

Last edited:

1,196,254
Messages
6,014,279
Members
441,811
Latest member
Peco73267326

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back