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
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.
Next I brought in my master product list, which has the product name, SKU, COGS expense, profit margins, and a few category levels:
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!