Dynamic lookup formula for moving Pivot Tables

Hi everyone,

I am working on a spreadsheet that needs to reference values found in a Pivot Table, which doesn't sound difficult to begin with, but let me just add the details.

The data in the spreadsheet must be displayed in a certain way, this is the layout of the Pivot Table (as an example):

Country	Retailer	Oct 17	Nov 17	Dec 17
USA	Walmart	400	600	500
	Target	100	150	120
	Gamestop	100	50	90
USA Total		999	999	999
UK	Asda	500	600	750
	Amazon	1000	1200	800
	PC World	400	500	600
	Argos	200	150	300
UK Total		999	999	999

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 have looked into possible trying to do a nested VLOOKUP but I can't seem to get it to work for my purposes. 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 incase 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

Please note the data from the pivot table is being pulled into a sheet with the same layout

So for example, in my spreadsheet, the cell that will pull the data for Argos, UK should be something like 'Find the UK in Column A, once found, find retailer Argos between UK and UK total' (I will wrap this in an IF formula for anything that throws an error.)

Please help! I have been stuck on this for so long, thanks!!

If I may ask, what is the difference between the "certain way" that the data in the spreadsheet must be displayed and the way pivot table itself displays the data?