Find most frequent word in column b associated with a certain value in column a

[TheExcelNoob]

Hi,

It's my first time using this forum and I have to admit I don't know much about excel.

So I have a list of parks in a column and a list of countries in another column.

An example would be:

Column A Column B
Central Park Spain
Central Park Spain
Banff Park Italy
Central Park Italy
Central Park


I'm trying to find which country is associated the most with Central Park, the answer would be spain

I have blank cells in the column of country so the result I get is #N/A.
The formula I used was =INDEX(B1:B5;MODE(IF(A1:A5="Central Park";EQUIV(B1:B5;B1:B5;0))))

I've done the opposite and it worked.


Thank you

[Special-K]

Not saying I'll answer this but I doubt anyone wants to retype that data as small as it is, so it's deiffficult to test any given solution.

Post a sample spreadsheet with expected results, remove any sensitive data, create a mockup example if necessary.
Use the Go Advanced option at the bottom of the page then scroll down to Manage Attachments as the "paperclip" method does not work on this forum.

[TheExcelNoob]

I've edited it, didn't know it was going to show like that, it was different when I clicked on preview. I hope it's a bit clearer.

[AliGW]

You were asked to post a sample workbook.

[Special-K]

See AliGW's reply first since she is a Forum Moderator.

Then consider,

What happens if there were 2 Central Parks for Spain and 2 Central Parks for Italy?
You'd obviously expect Spain and Italy to be output but in what format, Spain and Italy in one cell? Or a list saying

Spain
Italy

Or something else?