Index Match on dynamic columns and rows

[Sleurgh]

Hi,

I export automatically a set of marketing data coming from Google Analytics every month.
Column = Set of channels for my website (the number of channels can vary)
Countries = Showing how each channel is performing for each country in term of New Users (number of countries can change as well)

For reporting purposes, I'm trying to match let say "Organic Traffic" with "USA".
However, "USA" will always be on a different column every month depending on the performance. Similarly, "Organic Traffic" Will always be on a different row.

Is there any way to use index/match or vlookup functions to match dynamically two strings coming from column and row?

Any help would be appreciated, thank you in advance.

[AliGW]

Did you mean to post in the VBA section? I can move this for you if necessary.

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

[Sleurgh]

Hi,

I only use Excel formulas to automate my report. I don't think I will need to use VBA here.
I've attached a sample of my data with few comments and explanation notes.

Thank you

[AliGW]

Then you posted to the wrong section - I have moved the thread for you.

[Glenn Kennedy]

One way (this is for Jan.. similar for Feb):

=INDEX('January 22'!$1:$1048576,MATCH(Report!$A3,'January 22'!$A:$A,0),MATCH(B$2,'January 22'!$2:$2,0))

copied across and down.

BUT remember... matches must be exact. The value (orange) originally in A3 was NOT Organic search and returned no results. the value (yellow) originally in A4 had a trailing space "Social " and retirned no results.

[Sleurgh]

Hi Glenn,

Thanks a lot for your answer,your formula works perfectly!
The combination of 2 MATCH with INDEX was the solution to cover the dynamic aspect of both columns and rows.
Also yes you are right, the strings need to match exactly with no typo...
Have a pleasant weekend and thanks again for your time.

[Glenn Kennedy]

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.