Formula to find most common used text

Try array-entering this formula as in D1 below.

Formula:

=INDEX($B$1:$B$20,MIN(IF(MAX(COUNTIFS($A$1:$A$20,"QLD",$B$1:$B$20,$B$1:$B$20))=COUNTIFS($A$1:$A$20,"QLD",$B$1:$B$20,$B$1:$B$20),ROW($A$1:$A$20))))

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

	A	B	C	D
1	QLD	Book shelf		Book shelf
2	QLD	Book shelf
3	NSW	Chair
4	QLD	Pen
5	QLD	Table
6

Although since you have Excel 2010 try this formula instead. It does not have to be array-entered.

Formula:

=INDEX($B$1:$B$20,AGGREGATE(14,6,COUNTIFS($A$1:$A$20,"QLD",$B$1:$B$20,$B$1:$B$20)/($A$1:$A$20="QLD"),1))