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

Account

Sales Team

Segment

Ledger Code

Product Line

Client

Asset Class

Currency

Manager

Dec2016

Jan2017

Feb2017

Mar2017

Apr2017

Sold Direct

X Sales Team

A

ABC123

Insto

P

Alternative

Euro

F Mgr

0

100

2000

758

250

Sold Direct

Y Sales Team

B

ABC456

Insto

Q

Balanced

USD

G Mgr

2500

200

2500

1500

253

Sold Direct

Z Sales Team

C

ABC456

Insto

R

Market

CAD

H Mgr

2300

300

2000

3000

253

Sold Direct

A Sales Team

D

ABC123

Insto

S

Balanced

JPY

I Mgr

2458

400

4000

1000

253

Sold Direct

B Sales Team

E

ABC123

Insto

T

Alternative

Euro

J Mgr

0

500

4500

400

500

<tbody>
</tbody>

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

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

<tbody>
</tbody>

Thanks