How I Automate Spreadsheet Data Using INDEX & MATCH

It all comes together like a Rube-Goldberg machine

In This Post:

• A breakdown of how the index & match formula works

• How to create a sales spreadsheet that auto-populates the product data from another table

---

The Problem

I wanted Excel to auto-populate some product data in my Sales spreadsheet whenever a new order was recorded.

I’ve had a small Etsy shop for 12 years. It’s always been a set-it-and-forget-it passive income stream (trickle) that I never intended to manage like a real business. With over 1,800 orders, there’s a ton of data to play with. So today I decided to download it all and clean it up in Excel so I could have some fun with pivot tables.

In order to analyze this data at a significant level, each sale record needed to be matched with corresponding data in my product list spreadsheet (categories, COGS expense, profit margins, etc.)

I set up this advanced lookup using INDEX and MATCH formulas (using a data relation is another method that I’ll do a separate post on.)

---

The Solution I Crafted

First, I need to import all my sales data from Etsy. Annoyingly, you can only download one year of data at a time so this leaves me with 12 CSV files (ew.)

I could’ve combined them in Excel either via position or header title, but was quicker to just copy and paste it all into one sheet.

Gross. Can’t wait to clean this up and make it prettier.

Next I brought in my master product list, which has the product name, SKU, COGS expense, profit margins, and a few category levels:

I made all these tables orange because that's what color Etsy is :)

In Etsy, you have to make your item names these long strings of keywords and it’s a visual mess. I want to replace that with my actual item names.

To automate the process, I can use an INDEX and MATCH formula (the superior successor to V and H Lookups) that will pull in my shorter title by looking up the SKUs.

Problem is, not all of my Etsy orders have SKU codes as I only added them to Etsy in recent years. I’ll need to manually add it for several hundred of them. I can at least make it go faster by sorting the order list alphabetically to group any many similar items together as possible, and then bulk-pasting the appropriate SKUs.

Let’s see the Index & Match formula in action:

Pretty cool, right? It’s far better than V-Lookups because it doesn’t depend on the retrieved info being in a certain column order. If that gets changed, your lookup gets borked.

Here’s a closer look at what each part of the INDEX and MATCH formula is doing:

Next I added columns that extracted the year number and month numbers from each record so I could add slicers for quick data filtering. I love slicers.

That’s it for part one! I now have a beautiful, comprehensive sales record that I can easily update and sort.

In part 2, we finally get to the fun stuff: PIVOT TABLES AND PIVOT CHARTS!