INDEX MATCH MATCH Dynamic Named Lists

Hi everyone,

I am working on a report that needs to pull values found in a Pivot Table. The data in the spreadsheet must be displayed in a certain way to remain consistent with other corporate reports. The pivot tables are pulling data from an external source and they cannot be modified.

The issue I have is that this table is dynamic, and sometimes retailers will be added to the Pivot Table, other times they may be removed. Currently, for each country, I am drawing a unique VLOOKUP search area around those specific countries retailers in the Pivot Table, using the retailers names as the lookup values... but this takes a very long time as I have to draw these VLOOKUPs manually for well over 100 countries, and whenever the table moves (as Pivot Table updates pull in data from external sources) the VLOOKUP's often break, resulting in me having to redo the formulas.

I am trying to use INDEX MATCH MATCH but I can't seem to get it to work. I need to have a formula that can do the following:

• I must be able to draw my 'search area' around the ENTIRE Pivot Table (with excess in case the table grows), not small sections of it for each country
• The formula must be able to identify a country in the list, and then find instances of the retailers only for that country, and then pull the values for those retailers like a normal VLOOKUP
• The formula must be dynamic, and regardless of any changes to the pivot table, it must be able to successfully find the country and its respective retailers
  The formula must not be fooled by duplicate retailers appearing in more than one country

I am desperately trying to find an answer that doesn't propose reformatting my documents, or changing the layout. The layout of my reports cannot change as it needs to remain consistent with other reports which are not created by myself.

Please see attached document as an example, thank you!