A long time ago in a galaxy far far away, a small business owner who had a problem he wanted me to solve reached out to me. George had inherited a meat route with different prices for different customers, but for the same products. He needed a solution that could look up a price by both customer AND product. The solution? I created a customer and price matrix in Microsoft Excel.
And if you think this stuff is really advanced (and it is) then sign up and take my Excel courses right here on the site!
The key to the customer and price matrix in Microsoft Excel is the Index formula.
The index formula lets you do exactly what we need to do here. Look up a result in a matrix based on references to a row and a column.
The formula is actually pretty easy.
You set up your matrix of prices with customers in rows, and products in columns.
Then write the formula to grab a price based on a particular row and column:
=Index([matrix range],[Row #],[Column #])
The challenge is setting up a customer and price matrix in Microsoft Excel that isn’t based on a “particular” row and column, but rather based on something dynamic.
Something Excel can look up.
In order to accomplish this, we need to surround that matrix with the customers and products. Then in between we need the numbers that mark the row and column numbers of the matrix.
Next we write a nested formula. The index formula will contain a vlookup, and an hlookup based on a customer and product we’ve selected from a drop down.
This way we can select the customer and product from their respective lists. Then we can look up the price based on any combination of customer and product that we like.
The video will walk you through this step by step.
Before you start the video, open up a spreadsheet. Pause the video periodically so you can follow along by creating what I am teaching you here.
And remember! If you think this stuff is really advanced (and it is) then sign up and take my Excel courses right here on the site!