# INDEX MATCH MATCH multiple columns with same heading

1. ## INDEX MATCH MATCH multiple columns with same heading

Hi,

I am a new user and I have recently started a job in finance where I am getting the opportunity to become a power user of excel, unfortunately there aren't always people here to help me answer my questions. The error I am having today is a INDEX MATCH MATCH problem, when trying to put data into a more manipulatable form I need to run a index match match query. The problem I have is that there are repeats in the column header, so it only picks up the first instance , runs it to the bottom and then doesn't run it again. I have made up a simple spreadsheet that explains my problem, the original data is massive, hence the need for a formula. Using the example I have uploaded, I need a formula that will count all of the John columns, not just the first one.

The Three column table is what I need to populate so it can be manipulated more easily using other functions, as you can see the total sum of the 15 column table is \$335, and the sum of the cost column in the three column table is \$253.

2. ## Re: INDEX MATCH MATCH multiple columns with same heading

You need to create UNIQUE column headers. so JOHN PC, JOHN PSN, JOHN XB1, then you can use powerquery or pivot tables to UNpivot the data, or you can use your index match.

Are you trying to Aggregate the data so PSN + Xb1 + PC = COST?

3. Originally Posted by mikeTRON
You need to create UNIQUE column headers. so JOHN PC, JOHN PSN, JOHN XB1, then you can use powerquery or pivot tables to UNpivot the data, or you can use your index match.

Are you trying to Aggregate the data so PSN + Xb1 + PC = COST?
Thanks for the comment!

Changing the column header doesn't work given the size of the original data. The ultimate end result is to have a template where the qtrly data can be dumped into a sheet with a pivot table interrogating it so individual users can see what services they're subscribed to (and paying for) across different accounts.

4. ## Re: INDEX MATCH MATCH multiple columns with same heading

Can you not get the raw data in a format OTHER than this crosstabular report?

How many columns of data do you have in your real dataset?

I would likely process the data a bit to put platform into its own column, so each name stays unique. THEN use powerquery (or VBA for me) to unpivot the data completely.

Otherwise your method COULD work but there is a potential for error if you forget to manually add a concatenation of Game Title and Name (and/or platform).
http://chandoo.org/wp/2015/09/29/unp...h-power-query/

In the attached I manually processed the data in three steps, first to create a column for platform, then to stack names in a column THEN a final pivot that you can do whatever you want. At the end of the day ALL you want is a STANDARDIZED DATASET where everything has a unique column and all the data dimensions (platform & name & Title) are in their own column.

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1