array formula lookup spanning two columns

**bennunn** · 04-21-2013, 08:12 AM

Thanks to some help on this forum, I have a formula that looks up the most commonly occuring value in a column.

=INDEX('The List'!D$4:D$6001,MATCH(LARGE(COUNTIF('The List'!D$4:D$6001,'The List'!D$4:D$6001),1),COUNTIF('The List'!D$4:D$6001,'The List'!D$4:D$6001),0))

I'd now like this to span a two column range, but when I change the formula as follows, it doesn't work any more:

=INDEX('The List'!D$4:E$6001,MATCH(LARGE(COUNTIF('The List'!D$4:E$6001,'The List'!D$4:E$6001),1),COUNTIF('The List'!D$4:E$6001,'The List'!D$4:E$6001),0))

Any idea what I'm doing wrong?

Thanks

**dilipandey** · 04-21-2013, 08:37 AM

Hi Bennunn,

I am not sure about countif function syntax which you have applied in your formula, can you upload a sample workbook along with your expected output. Thanks.

Regards,
DILIPandey
<click on below * if this helps>

**Lemice** · 04-21-2013, 08:40 AM

Hello,

You can try this formula instead

Please Login or Register  to view this content.

I changed the reference in my working formula with the matching reference from your formula, should work though ...

**martindwilson** · 04-21-2013, 08:43 AM

to just get the most common number in a range use mode()

=MODE('the list'!D4:E6001)

**Lemice** · 04-21-2013, 08:53 AM

I feel funny after I read your solution Martin ... I didn't know that such a formula actually exists ...

**martindwilson** · 04-21-2013, 08:55 AM

mind you mode() wont work with text!

**bennunn** · 04-21-2013, 11:23 AM

I've stripped down my file, added dummy data and attached it here.

MODE won't work because I'm trying to count strings.

What I'm after is a top five most commonly occuring strings (which I can do, but only from a single column - I need a two column span).

Thanks.