How to Return Most Common Value in a Table

Try

C2=SUMPRODUCT(--(A2&B2=$A$2:$A$14&$B$2:$B$14))

and searching word in on cell K5

M5=INDEX(OFFSET($A$1,MATCH(K5,$A$2:$A$14,0),1,COUNTIFS($A$2:$A$14,K5),),MAX(OFFSET($A$1,MATCH(K5,$A$2:$A$14,0),2,COUNTIFS($A$2:$A$14,K5),)))

Check the attached file.

Try this...

Data Range

	A	B	C	D	E
1	Nissan	Fred	------	Nissan	Fred
2	Nissan	Fred
3	Nissan	Fred
4	Nissan	Mark
5	Nissan	Mark
6	Ford	Mark
7	Ford	Mark
8	Ford	Mark
9	Ford	Mark
10	Ford	George
11	Toyota	George
12	Toyota	George
13	Toyota	Fred

This array formula** entered in E1:

=INDEX(B1:B13,MODE(IF(A1:A13=D1,MATCH(B1:B13,B1:B13,0))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.