Index Match Lookup

Hi,

I am creating a pivot table (on the pivot table tab in the attached on link) and I need it to look up into a static table (on the lookups tab). I am struggling to get it lookup up to the right line and locate the correct month and input the value. I think it will be an index match formula, can anyone help please?

Thankshttps://1drv.ms/x/s!Algf4bOCiK4DgVTkZI3CHNx_FR74

https://1drv.ms/x/s!Algf4bOCiK4DgVTkZI3CHNx_FR74

Hi, thanks.

The pivot feeds from a raw table into the pivot (I have recreated the pivot in the pivot tab on the sheet - it will always be the same size). In column F on the pivot tab I created a concatenation that may or may not be helpful.

Essentially we need to feed the table in the tab 'lookups' from the data in the tab 'pivot tables'. For example cell D20 in 'Lookups' needs to find the box in the pivot for 'sum of sales' 'financial year 2016-2017' for June. So it needs to pull back cell N8 from the 'pivot table' tab and enter that.

Does that make sense - hugely appreciate your support here.

Your 2 tables need to be organized for the months to be in the same order. Please organize this way and send the attachment. I have a meeting now and will address the issue when I come back.

This will work very well with Index Match array.

Thank you.

I have added attached the spreadsheet as requested.

I have also rearranged the tables to match however you will see that in C4 of the lookups tab I can select what month I wish my year to start at, as such it will not always be in the same order as the pivot, as such I was hoping the formula would be able to match the month regardless of the pivot table order - extra complications, I know!

You are very inconsistent is your "naming" e.g Jan vs January. To avoid this problem use Excel dates (01/10/2016) ad then format as "mmm" or "mmmm".

In your "Pivot Table" you could use 2015 as the year in the headings as you can work out the row from the year.

Try (for Sales 2016/2017)

=INDEX('Pivot Table'!I8:T13,MATCH(Lookups!C6,LEFT('Pivot Table'!G8:G13,4)+0,0),MONTH($C$4&0))

Enter with Ctrl+Shift+Enter

There is no parameter in "Lookups" to identify which data you want returned. (Sales, Forecast, Budget)

Hi, Thanks for your response - that formula works for that particular cell but I am unsure how to extend it out.

I have uploaded the workbook this time with how I am trying to get the data to appear. I understand what you mean about there being no identifier but this is where I thought the concatenation column that I added might come in to play, it is visible in this workbook. Could be that what I am trying to do is not possible though.

If you have any ideas I would certainly appreciate them.

In D20

=INDEX('Pivot Table'!$I$8:$T$13,MATCH(Lookups!$A20,'Pivot Table'!$F$8:$F$13,0),MONTH(D$18&0))

Copy across and down

Glad John was able to figure out the issues.

That works perfectly, thank you so much for your help, really appreciated.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.