INDEX MATCH MATCH with multiple layered tables

[acenewbie]

I wish to conduct a INDEX MATCH MATCH formula to retrieve the information from source A and paste into destination B. I want to be able to drag the formula across the cells in destination B and have them auto-populate the data automatically.

Please review the following image.

ex11.png



Once I have started this, I have another issue:

ex22.png

As you can see in the second attachment, I have more criteria to meet. I need to have a formula that can extract selective data from source A, and paste it into destination B, without getting confused of multiple retailers with the same name in the same country.

Can someone either send me an example Excel file, or give me a thorough explanation how I could make this work?

Please assume in all answers that I cannot change the source or destination layout or formatting

Thanks for your help!

[sandy666]

Attach a sample workbook (pictures are useless!). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[acenewbie]

Hi Sandy,

I have attached a workbook example - all the data is dummy data, I replaced it before uploading pictures. I tried to use the attachment button but the dialogue to upload never came up. I used the manage attachments option like you suggested and that worked

What I am doing is very simple, so I just wrote in the cells on the right side what I am expecting to appear in those cells in a successful formula

[sandy666]

something like this?
Done with PowerQuery and PivotTable

[acenewbie]

Hi Sandy,

Thanks for the help, but its not exactly what I am looking for. The data being extracted from my source is from a PivotTable - so I need to format it nicely in a report, thus I cannot use another Pivot table to get the job done.

Up until now I have been using VLOOKUP, but this obviously has massive limitations, as the PivotTables being used are pulling data from an external database, and whenever they are refreshed they often move, elements from lists can be added, etc.

I was hoping an INDEX MATCH MATCH formula would enable me to get the data across easily, however this breaks the moment I try to get data from a retailer in a country that is not on the same row as the country name, for example if I wanted to get data for Citroen in France, I can use the formula =INDEX(Core_Table,MATCH($H11,Core_Countries,0),MATCH(K$10,Core_Columns,0)), however since Pivot Tables only have one sub-heading for the country, I need to have a formula that will look at France, then stop, move one column to the right, and then pull the data for all elements listed in France. I was told a COUNTA formula could be nested in here somewhere to help Excel determine when to stop, but its starting to get very complicated

[sandy666]

So you need prepare your source to be a proper table: single header row, and proper columns with the same type of data - then you can try to use INDEX/MATCH

[acenewbie]

Hi Sandy,

As I mentioned in my first post, I can't modify the source, there is no way to get the country name to be listed on each row in the Pivot Table - and I also have no control over what data is pulled as it is from an external source with which my viewing rights are limited (I am only allowed to see certain filters on these pivot tables depending on my access privileges).

A gentleman on my previous post sent me a file that was pretty much perfect, but only two things were wrong - it was provided as a drop-down selection, which is not the layout I need, and it was seriously complex without much explanation, I have reached out to him by private message and I am awaiting a response, but I was wondering if there was an easier way to do this

[sandy666]

So if you cannot change your source (looks like a copied pivot table without real pivot table) you can use PowerQuery to prepare your own table then you can use what you want
or I missed something

[acenewbie]

I've never used PowerQuery before, but I cannot install Excel add-ons as this is a restricted corporate machine

[sandy666]

So I can't help
Maybe someone else will give you a solution

have a nice day