# MODE vs INDEX vs LOOKUP

#### William Groff

##### New Member
I'm trying to calculate the frequency of recurring numbers from multiple columns. I used MODE (=MODE(\$B\$4:\$B\$108))) in the first row for the most frequent number, and for the 2nd most frequent I used

=INDEX(ROW(INDIRECT("B"&MIN(\$B\$4:\$B\$108)&":B"&MAX(\$B\$4:\$B\$108))),MATCH(LARGE(FREQUENCY(\$B\$4:\$B\$108,ROW(INDIRECT("B"&MIN(\$B\$4:\$B\$108)&":B"&MAX(\$B\$4:\$B\$108)))),2),FREQUENCY(\$B\$4:\$B\$108,ROW(INDIRECT("B"&MIN(\$B\$4:\$B\$108)&":B"&MAX(\$B\$4:\$B\$108)))),FALSE))

However, when I change highlighted number to '3' for the third most frequent (and so on for 4th and 5th), I end up with a repeated number. The second (and subsequent) formula was entered as an array, but after three days, I've given up trying to figure it out. I really do need help understanding what I am doing incorrectly.

Thanks in advance for any assistance you can provide.

This table is supposed to report the highest frequency occurrence for each column of the source data, in descending order (with 5th highest frequency on the bottom - rank order). The actual results are shown in each cell. As you can see, some numbers repeat.
 H I J K L M N O 1 RANK 1 2 3 4 5 6 2 1 3 28 50 64 69 8 3 2 5 25 21 59 64 25 4 3 2 12 21 29 67 12 5 4 2 11 15 31 61 3 6 5 2 11 15 29 61 3

<tbody abp="1111">
</tbody>

Table with source data:

A B C D E F G
 DATE 1 2 3 4 5 6 2-Jan-2016 5 6 15 29 42 10 6-Jan-2016 2 11 47 62 63 17 9-Jan-2016 16 19 32 34 57 13 13-Jan-2016 4 8 19 27 34 10 16-Jan-2016 3 51 52 61 64 6 20-Jan-2016 5 39 44 47 69 24 23-Jan-2016 22 32 34 40 69 19 27-Jan-2016 3 12 40 52 67 21 30-Jan-2016 5 12 16 31 43 18 3-Feb-2016 26 28 31 36 52 8 6-Feb-2016 4 13 31 36 52 8 10-Feb-2016 2 3 40 50 62 5 13-Feb-2016 7 15 18 19 36 20 17-Feb-2016 7 17 27 29 40 25 20-Feb-2016 11 12 15 16 54 25 24-Feb-2016 21 31 64 65 67 5 27-Feb-2016 10 11 21 22 53 18 2-Mar-2016 12 13 44 52 62 19 5-Mar-2016 3 27 34 59 69 19 9-Mar-2016 14 23 32 34 68 3

<tbody abp="826">
</tbody><colgroup abp="1022"><col abp="1023"><col span="5" abp="1024"><col abp="1025"></colgroup>

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

#### Canapone

##### Active Member
Hi,

if J2 houses

=MODE(B\$4:B\$108)

in J3 (array entered) to be copied across:

=MODE(IF(COUNTIF(J\$2:J2,B\$4:B\$108)=0,B\$4:B\$108))

Hope it helps

##### MrExcel MVP
@William Groff

Does the upper exhibit showing your formula results or the desired results i.e. the results which must obtain?

#### Joe4

Cross posted here: MODE vs INDEX

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule #13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Replies
4
Views
249
Replies
4
Views
470
Replies
3
Views
722
Replies
8
Views
339
Replies
1
Views
2K

1,190,896
Messages
5,983,437
Members
439,843
Latest member
PlanetFitness

### 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