Index Match Lookup

[mackcracknsack]

Hi,

I am looking to use a Index Match formula to look up data from a pivot table.
I have most of the formula complete but I cannot figure out how to implement a match to pull data from my rows of my pivot.

=INDEX('[2018 Pivot tables.xlsb]Pivot'!$C$11:$S$242,2,MATCH(B13,'[2018 Pivot tables.xlsb]Pivot'!$C:$S,0))

So, I am trying to replace the blue 2 in the above formula with a match, that will pull in the rows of my pivot as I drag my formula across different columns.

Any help greatly appreciated.

Thanks,

 

[jorismoerings]

If you were trying to pull data from a pivot table why don't use GETPIVOTDATA. This solves silly things like you adding an extra filter causing your INDEX range to change, etc.
The function will most probably be more efficient than using INDEX.

If you share some sample data of the way your pivot looks and create a mockup of what you're trying to do with the INDEX function, we will be able to come up with answer as this general one.
Hope it helps you anyway.

 

[mackcracknsack]

Thanks for the reply,

My source data / pivot looks something like below.
I want to pull this data into a large financial model, which also has some other data feeding from another source.
I want to update the model monthly and I want to just refresh my pivot which will also pull in new data from the pivot to the model.
I use the bold items as my lookups.


Source / Pivot

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Account[/TD]
[TD]Sales Team[/TD]
[TD]Segment[/TD]
[TD]Ledger Code[/TD]
[TD]Product Line[/TD]
[TD]Client[/TD]
[TD]Asset Class[/TD]
[TD]Currency[/TD]
[TD]Manager[/TD]
[TD]Dec2016[/TD]
[TD]Jan2017[/TD]
[TD]Feb2017[/TD]
[TD]Mar2017[/TD]
[TD]Apr2017[/TD]
[/TR]
[TR]
[TD]Sold Direct[/TD]
[TD]X Sales Team[/TD]
[TD]A[/TD]
[TD]ABC123[/TD]
[TD]Insto[/TD]
[TD]P[/TD]
[TD]Alternative[/TD]
[TD]Euro[/TD]
[TD]F Mgr[/TD]
[TD]0[/TD]
[TD]100[/TD]
[TD]2000[/TD]
[TD]758[/TD]
[TD]250[/TD]
[/TR]
[TR]
[TD]Sold Direct[/TD]
[TD]Y Sales Team[/TD]
[TD]B[/TD]
[TD]ABC456[/TD]
[TD]Insto[/TD]
[TD]Q[/TD]
[TD]Balanced[/TD]
[TD]USD[/TD]
[TD]G Mgr[/TD]
[TD]2500[/TD]
[TD]200[/TD]
[TD]2500[/TD]
[TD]1500[/TD]
[TD]253[/TD]
[/TR]
[TR]
[TD]Sold Direct[/TD]
[TD]Z Sales Team[/TD]
[TD]C[/TD]
[TD]ABC456[/TD]
[TD]Insto[/TD]
[TD]R[/TD]
[TD]Market[/TD]
[TD]CAD[/TD]
[TD]H Mgr[/TD]
[TD]2300[/TD]
[TD]300[/TD]
[TD]2000[/TD]
[TD]3000[/TD]
[TD]253[/TD]
[/TR]
[TR]
[TD]Sold Direct[/TD]
[TD]A Sales Team[/TD]
[TD]D[/TD]
[TD]ABC123[/TD]
[TD]Insto[/TD]
[TD]S[/TD]
[TD]Balanced[/TD]
[TD]JPY[/TD]
[TD]I Mgr[/TD]
[TD]2458[/TD]
[TD]400[/TD]
[TD]4000[/TD]
[TD]1000[/TD]
[TD]253[/TD]
[/TR]
[TR]
[TD]Sold Direct[/TD]
[TD]B Sales Team[/TD]
[TD]E[/TD]
[TD]ABC123[/TD]
[TD]Insto[/TD]
[TD]T[/TD]
[TD]Alternative[/TD]
[TD]Euro[/TD]
[TD]J Mgr[/TD]
[TD]0[/TD]
[TD]500[/TD]
[TD]4500[/TD]
[TD]400[/TD]
[TD]500[/TD]
[/TR]
</tbody>[/TABLE]


Above Pivot table data will feed into my below model, which I want to update each month with new data from the pivot table


Financial Model

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Account[/TD]
[TD]Sold Direct[/TD]
[TD]Sold Direct[/TD]
[TD]Sold Direct[/TD]
[TD]Sold Direct[/TD]
[TD]Sold Direct[/TD]
[/TR]
[TR]
[TD]Sales Team[/TD]
[TD]X Sales Team[/TD]
[TD]Y Sales Team[/TD]
[TD]Z Sales Team[/TD]
[TD]A Sales Team[/TD]
[TD]B Sales Team[/TD]
[/TR]
[TR]
[TD]Segment[/TD]
[TD]A [/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]Product Line[/TD]
[TD]Insto[/TD]
[TD]Insto[/TD]
[TD]Insto[/TD]
[TD]Insto[/TD]
[TD]Insto[/TD]
[/TR]
[TR]
[TD]Asset Class[/TD]
[TD]Alternative[/TD]
[TD]Balanced[/TD]
[TD]Market[/TD]
[TD]Balanced[/TD]
[TD]Alternative[/TD]
[/TR]
[TR]
[TD]Territory[/TD]
[TD]Europe[/TD]
[TD]US[/TD]
[TD]Canada[/TD]
[TD]Asia[/TD]
[TD]Europe[/TD]
[/TR]
[TR]
[TD]Currency[/TD]
[TD]Euro[/TD]
[TD]USD[/TD]
[TD]CAD[/TD]
[TD]JPY[/TD]
[TD]Euro[/TD]
[/TR]
[TR]
[TD]Manager[/TD]
[TD]F Mgr[/TD]
[TD]G Mgr[/TD]
[TD]H Mgr[/TD]
[TD]I Mgr[/TD]
[TD]J Mgr[/TD]
[/TR]
[TR]
[TD]Desk[/TD]
[TD]K Desk[/TD]
[TD]L Desk[/TD]
[TD]M Desk[/TD]
[TD]N Desk[/TD]
[TD]O Desk[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dec2016[/TD]
[TD]0[/TD]
[TD]2500[/TD]
[TD]2300[/TD]
[TD]2458[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Jan2017[/TD]
[TD]100[/TD]
[TD]200[/TD]
[TD]300[/TD]
[TD]400[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]Feb2017[/TD]
[TD]2000[/TD]
[TD]2500[/TD]
[TD]2000[/TD]
[TD]4000[/TD]
[TD]4500[/TD]
[/TR]
[TR]
[TD]Mar2017[/TD]
[TD]758[/TD]
[TD]1500[/TD]
[TD]3000[/TD]
[TD]1000[/TD]
[TD]400[/TD]
[/TR]
[TR]
[TD]Apr2017[/TD]
[TD]250[/TD]
[TD]253[/TD]
[TD]253[/TD]
[TD]253[/TD]
[TD]500[/TD]
[/TR]
</tbody>[/TABLE]


Thanks