+ Reply to Thread
Results 1 to 16 of 16

Lookup to a Moving Lookup Reference

  1. #1
    Registered User
    Join Date
    10-25-2019
    Location
    North Carolina
    MS-Off Ver
    Office 365 MSO (16.0.11328.20420) 64-bit
    Posts
    8

    Lookup to a Moving Lookup Reference

    I'm trying to write a formula that will lookup values from a data set based on a pivot table filter. I was using a vlookup but it breaks when new filters are added/moved around because the lookup value moves as a result. What formula can I use to lookup a moving lookup reference? The data is not being pulled from the pivot table itself (because I want to show that info across the top, not in the pivot) but from the data set used to create the pivot.

    Thank you!
    Attached Files Attached Files
    Last edited by mv0826; 10-25-2019 at 09:58 AM.

  2. #2
    Valued Forum Contributor dosydos's Avatar
    Join Date
    12-09-2015
    Location
    Massachusetts
    MS-Off Ver
    Microsoft Office 365
    Posts
    1,148

    Re: Lookup to a Moving Lookup Reference

    my guess would be an index match match formula, but it is hard to truly tell unless you include a sample copy of your workbook.

  3. #3
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,770

    Re: Lookup to a Moving Lookup Reference

    Without sample file. It is bit hard to help you.

    Read the top banner and follow instruction to upload desensitized sample of your file.
    Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.
    Typically, you'd set up dynamic named range(s) to accomplish what you are after.
    “Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.”
    ― Robert A. Heinlein

  4. #4
    Registered User
    Join Date
    10-25-2019
    Location
    North Carolina
    MS-Off Ver
    Office 365 MSO (16.0.11328.20420) 64-bit
    Posts
    8

    Re: Lookup to a Moving Lookup Reference

    I've added an example. Hopefully that helps.

  5. #5
    Registered User
    Join Date
    10-25-2019
    Location
    North Carolina
    MS-Off Ver
    Office 365 MSO (16.0.11328.20420) 64-bit
    Posts
    8

    Re: Lookup to a Moving Lookup Reference

    Just added an example. I was trying to use a vlookup to get the info from the data set based on the "Lookup Value" filter. However, if other filters are added, the lookup value moves, so I need something more dynamic.

  6. #6
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,770

    Re: Lookup to a Moving Lookup Reference

    It isn't clear what you are after really...

    FYI - I would avoid use of Merged ranges. It's one of the worst thing you can do to structured data.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,012

    Re: Lookup to a Moving Lookup Reference

    It isn't clear at all!!!

    What are you expecting to see in those yellow cells? Give us the values.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  8. #8
    Registered User
    Join Date
    10-25-2019
    Location
    North Carolina
    MS-Off Ver
    Office 365 MSO (16.0.11328.20420) 64-bit
    Posts
    8

    Re: Lookup to a Moving Lookup Reference

    I want to pull information from the data set (contract types, etc.).

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,012

    Re: Lookup to a Moving Lookup Reference

    I asked you to give us values - I have no idea what it's meant to look like once this information has been pulled through. Where is the information that you want to pull through?

  10. #10
    Registered User
    Join Date
    10-25-2019
    Location
    North Carolina
    MS-Off Ver
    Office 365 MSO (16.0.11328.20420) 64-bit
    Posts
    8

    Re: Lookup to a Moving Lookup Reference

    I updated the example. Hopefully that helps. I also added the formula I've been trying to use in D2. This works until filters are added/moved around because the lookup value moves and the vlookup returns #N/A.

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,012

    Re: Lookup to a Moving Lookup Reference

    Where are you adding and moving around filters? Give us a practical, step-by-step example of what you are at the moment only vaguely describing.

  12. #12
    Registered User
    Join Date
    10-25-2019
    Location
    North Carolina
    MS-Off Ver
    Office 365 MSO (16.0.11328.20420) 64-bit
    Posts
    8

    Re: Lookup to a Moving Lookup Reference

    If I open the Field List and move the additional filter up, the "Additional Filter" filter becomes B1 and the "Lookup Value" filter becomes B2. My Vlookup in cell D2 references B1, so it returns #N/A once filters are moved.

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,012

    Re: Lookup to a Moving Lookup Reference

    Why do you want/need to move anything? Are you not making problems for yourself?

    I'm struggling to see the bigger picture here, sorry.

  14. #14
    Registered User
    Join Date
    10-25-2019
    Location
    North Carolina
    MS-Off Ver
    Office 365 MSO (16.0.11328.20420) 64-bit
    Posts
    8

    Re: Lookup to a Moving Lookup Reference

    This is a highly simplified version of this report, which will be distributed and manipulated by users. They may change the filters based on whatever they're trying to see.

  15. #15
    Registered User
    Join Date
    10-25-2019
    Location
    North Carolina
    MS-Off Ver
    Office 365 MSO (16.0.11328.20420) 64-bit
    Posts
    8

    Re: Lookup to a Moving Lookup Reference

    I added a sample. I hope that helps!

  16. #16
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,012

    Re: Lookup to a Moving Lookup Reference

    I can't help thinking that a table with slicers might serve you better here than a pivot table.
    Attached Files Attached Files
    Last edited by AliGW; 10-25-2019 at 10:23 AM.

+ 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. Moving Forecast based on Lookup Templates
    By sasha38 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 04-11-2014, 02:50 PM
  2. Two Criteria Lookup with Moving Starting Points
    By ImTheNeonTiger in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-17-2013, 12:56 AM
  3. Lookup for moving months
    By amartino44 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 05-03-2013, 02:19 PM
  4. Lookup for moving months
    By amartino44 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-03-2013, 01:20 AM
  5. Conditional lookup and moving of addresses
    By sabbur in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-25-2009, 02:14 PM
  6. Moving Sum Lookup
    By DButtgen in forum Excel - New Users/Basics
    Replies: 6
    Last Post: 05-01-2009, 10:51 AM
  7. Moving Lookup Function - A solution you might want to use
    By PeterB in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-15-2007, 05:02 AM

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