+ Reply to Thread
Results 1 to 5 of 5

Dynamic lookup formula for moving Pivot Tables

  1. #1
    Registered User
    Join Date
    02-21-2018
    Location
    England
    MS-Off Ver
    Office Professional Plus 2013
    Posts
    15

    Question 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!!
    Last edited by acenewbie; 03-07-2018 at 12:02 PM.

  2. #2
    Registered User
    Join Date
    08-26-2014
    Location
    gent
    MS-Off Ver
    2010
    Posts
    19

    Re: Dynamic lookup formula for moving Pivot Tables

    You can use ranges to define your lists.
    I added an example excel file you can use to start form
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    02-21-2018
    Location
    England
    MS-Off Ver
    Office Professional Plus 2013
    Posts
    15

    Re: Dynamic lookup formula for moving Pivot Tables

    This looks amazing, thank you! I will see how it implements when I start my next month report. I will have to make a few tweaks and understand more about how this works, I will get back to you if I have any issues

  4. #4
    Registered User
    Join Date
    02-21-2018
    Location
    England
    MS-Off Ver
    Office Professional Plus 2013
    Posts
    15

    Re: Dynamic lookup formula for moving Pivot Tables

    Is there any chance I could see what this looks like in a format that isn't a drop-down menu? I am trying to understand the formulas at work here since there are so many, as I need to implement this on a massive worksheet for a company and it is imperative that I fully understand this before implementation in case something goes wrong. Thank you so far for your amazing response!

  5. #5
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    13,921

    Re: Dynamic lookup formula for moving Pivot Tables

    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?
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Help with excel formula-lookup/pivot tables
    By JKKANG254 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-21-2020, 03:39 PM
  2. Can Pivot Tables be Dynamic?
    By jtilleyx in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 07-27-2016, 07:11 AM
  3. Excel lookup formula when using multiple pivot tables
    By sweetpea12 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-26-2013, 03:30 PM
  4. Replies: 0
    Last Post: 09-22-2012, 07:22 PM
  5. Pivot tables with dynamic filters
    By Scotty81 in forum Excel General
    Replies: 0
    Last Post: 05-08-2009, 11:04 AM
  6. Dynamic Pivot Tables
    By calli in forum Excel - New Users/Basics
    Replies: 0
    Last Post: 05-07-2007, 04:00 PM
  7. Dynamic Pivot tables
    By Phil in forum Excel General
    Replies: 3
    Last Post: 01-31-2005, 12:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1