Pulling Data from a Pivot Table using Index/Match .

  • First time visitor with a headache.
    I'm just learning about Pivot Tables (P-T), and I need someones help in how to extract data.


    I will try and make this simple.
    Sheet1 (SHT1) has my Bill of Materials for an assembly.
    Sheet2 (SHT2) has my P-T.


    Sht1 lists all my P/N's in Col B.
    In COL M, I need to pull the Max dollar from 3 different Vendors from my P-T, and place it in cell M12 of Sht1.


    Each P/N has 4 qty's associated with it.
    In otherwords, P/N 123 needs prices quoted for 50, 100, 250, 500 pcs each.


    My P-T lists the P/N's on the left,
    then the Qty's are listed next.
    Across the top lists all my Vendors, and their pricing for each P/N they quoted.
    Let's say the Vendors run from C4:AA4.


    Is there a formula I can enter in M12 of SHT1, that takes the P/N in B12, goes to the P-T, matches it, and the specific qty, then pulls the MAX value from a range and places it here?


    I'm plugging away trying to find the right INDEX/MATCH scenario, but I haven't hit it yet..


    Thanks for your help,
    Zac

  • Hi


    Welcome to the board


    It might be helpful if you use the browse button to attach an example to your query.


    I haven't seen this done, but it might be possible. :thumbup:

  • Hi Zac.


    There is a function called GETPIVOTDATA which is used to extract data from pivot tables.


    If it doesn't work for you, post the file and I'll see if I can help you.


    HTH
    /Fredrik

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!