Most Common text result in a selection If certain value present

Here's my sheet showing the data I'm trying to collect on a separate front table

Screen Shot 2015-06-18 at 5.51.08 PM.png

I'm trying to display the most common "Ad Source" for all calls placed to "Clinic Location" "ME"

Here's my formula
=IF(('PCC Call Data'!J3:J246,(INDEX('PCC Call Data'!B3:B246,MODE(IF(('PCC Call Data'!B3:B246<>"")*ISNA(MATCH('PCC Call Data'!B3:B246,$Z$466:$Z466,0)),MATCH('PCC Call Data'!B3:B246,'PCC Call Data'!B3:B246,0)))))),"")

I placed the "IF" statement at the front of the "INDEX" statement but I'm not getting a positive result. the INDEX statement functions well on its own, grabbing the most common result, but I can't expand it to include the IF statement for each different locations.

I'm looking for location-specific results for our inbound marketing campaigns.

Appreciate any help I can get.
Thanks!
Alex

Probably main reason why it is not answered for quite a long time is ... no data to test, so:

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

I think that checking location shall go inside MODE part, so:

Formula:

=INDEX('PCC Call Data'!$B$3:$B$426,MODE(IF(('PCC Call Data'!$B$3:$B$426<>"")*('PCC Call Data'!$J$3:$J$426=H4)*ISNA(MATCH('PCC Call Data'!$B$3:$B$426,$Z$466:$Z$466,0)),MATCH('PCC Call Data'!$B$3:$B$426,'PCC Call Data'!$B$3:$B$426,0))))

of course array formula, and changed addressing to absolute - othewise with copying down the referenced ranges in PCC Call data sheet would move

In K4 on "Display Sheet":

Formula:

=COUNTIFS('PCC Call Data'!$F$3:$F$458,"<>",'PCC Call Data'!$J$3:$J$458,H4)

and copy down

Closed at AMeloche's request to re-open in Commercial Services.