Using Index Match within a non-standard table

Question

I have a daily performance manager for Google Ads and the like. Each day or every few days I copy basic metric data into the table and I can see performance and budget spend over time.

It looks something like this:

date        Clicks Impression CTR   Cost Conversions CPA   CPC   CVR
01/03/2019  23       150      15%    $20   2         $10  $0.67  8.7%
02/03/2019  23       150      15%    $20   2         $10  $0.67  8.7%
03/03/2019  23       150      15%    $20   2         $10  $0.67  8.7%
04/03/2019  23       150      15%    $20   2         $10  $0.67  8.7%
05/03/2019  23       150      15%    $20   2         $10  $0.67  8.7%
06/03/2019  23       150      15%    $20   2         $10  $0.67  8.7%
07/03/2019  23       150      15%    $20   2         $10  $0.67  8.7%
08/03/2019  23       150      15%    $20   2         $10  $0.67  8.7%
09/03/2019  23       150      15%    $20   2         $10  $0.67  8.7%

An so on until the end of the month. This repeats for each month. At the end of each Month section it has the name of the month and the totals for each month.

I manage a lot of clients and I've made one Excel Workbook that allows me to do this all with a sheet for each client. My issue is that I would like a dashboard that has a snapshot of each client's performance across the month to make it easier for myself.

My first attempt was using INDEX MATCH, however, you need a table for it to work. So I attempted to use named ranges to create the table. I created a Client1StatsTable where I selected just the rows with the month totals and a Client1MonthName range which had the month name.

This meant that I thought I could run the range in an INDEX MATCH as if it were a real table. I wrote the following 2 formulas:

INDEX(Client1StatsTable, MATCH(TEXT(DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())),"mmmm"),Client1MonthName,0), 3)  
INDEX(Client1StatsTable,MATCH(K12,Client1MonthName,0),3)

I would repeat this for each client to get a month-to-date snapshot of the performance across the different metrics.

However, I got the #N/A error. When I ran it through the Evaluate Formula function in Excel I found that even though the range was parsed correctly as multiple rows and cells that were disconnected, it got stuck when it tried to place the Month in MATCH.

It parsed the month correctly, but just couldn't find it in the range.

The end table should have every client in one table and look like:

Date     ClientName    CTR  CPC  CPA  CVR  Spend  Amount of Budget Used
04/03/2019  Client 1    5%   $0.86  $15  2%   $30   15%

I would preferably like to accomplish this without using VBA.

Pivot table would only theoretically work for Year-to-Date snapshot that's represented by the 2nd formula. Because in that case I would have to create a separate table for each client. For the month-to-date, I'm looking to create a snapshot for the current month for each client in one table. It would like something like: Date Client Name CTR CPC CPA CVR Spend Amount of Budget Used — Jknight
INDEX and MATCH don't need the data to be in a table: the indexed range can be on sheet 2 and the match range on sheet 1, all that is needed is for both ranges to be the same length. — Solar Mike
Both formulas worked for me. I did have to change the date formatting to "MMMM" though since "mmmm" was minutes instead of months (this may be system-language dependent). You may also want to look into PowerQuery (add-in for 2010/2013, integrated in 2016) for this if you haven't already. — hsan
@SolarMike yes, but based on Evaluate Formula tool. It gets stuck within MATCH By itself — Jknight

Adav Adav · Accepted Answer · 2019-03-04T09:51:54

You can recreate a pivot table to your convienience using sumproduct-filters with booleans

=Sumproduct(--(condition_1);--(condition_2); Sum_of_range)

Here's with your example:

CTR for client A at date 4/3/19

= Sumproduct(--(Client_range='A');--(Date_range='4/3/19'); Sum_of_CTR)

Do the same for the rest of the fields of your dashboard

Using Index Match within a non-standard table

2 Answers