In this tutorial we are going to do a lookup from one table that has multiple quantities for the same items, and ensure that we only pull each unique quantity once into another table sorted in a different order. Let’s see how to do this in Excel.
You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.
So here we have one table that has 13 items of different quantities. You can see that some of these are the same products, just in different locations. I want to pull each unique quantity into the table on the right. Note on the right table that the items are sorted in a different order:
Here are the various functions that we will use and their syntax:
The formula we are going to use for this feat is:
Notice by the curly brackets that this is an array formula, thus needing to be entered using Ctrl + Shift + Enter.
If we tried to use a simple VLOOKUP formula, here would be the result:
You can see that the formula keeps pulling the first instance of the Item that it comes across, not pulling unique ones each time. So that won’t work.
So, let’s enter our formula and see what happens:
If you check each instance of each Item, you’ll see that there are no repeating quantities, and the total for both tables match.
So, how does the formula work? If we break down the INDEX formula, there are two parts to the syntax: array and row number. The array is simple – it’s $C$2:$C$14 which are the quantities from the left table. The rest of the formula defines the row number. Since the column number of the INDEX formula is optional, and we only have one column in our array, that is not necessary.
The key to this formula is how we determine the row number to pull for each of the formulas in column H. That is determined by the SMALL function of the formula:
The SMALL function is broken into two components: the array and the k number. The array is determined by the IF function, which includes the ROW function, and the k is determined by the COUNTIF function. So the IF function (including the ROW function) determines the list of variables, and the COUNTIF function determines which to choose for each row.
Let’s look at the k portion of the syntax for the SMALL function that is determined by the COUNTIF formula. COUNTIF has two components to its syntax: range and criteria. The range is locked at the top of the list ($G$2) and as it is copied down, it contains all the cells to that point. The criteria is the Item in column G. So as the range is expanded, the COUNTIF function will determine the instance number of the Item in that row.
Let’s take a look at the seventh item in our list:
The COUNTIF function will return a 4, which is the 4th time Product3 appears in the range from G2:G8.
The IF function determines the array for the SMALL function. The formula for that row is:
So, this being an array formula, the logical test of G8=$A$2:$A$14 returns:
This shows a TRUE for items 6, 7, 8, 9, and 10 on the list. The ROW function, which is the same for all the formulas, (note the minus 1 at the end) returns the following:
Therefore, by virtue of this being an array formula, the IF function gives us the following:
So with the array for the SMALL function being 6, 7, 8, 9, and 10, and the k from the COUNTIF function being 4, the 4th item in that array is 9. That is the row number for our INDEX function.
So, let’s get back to our INDEX function. The array is $C$2:$C$14 and the row number is 9. That gives is a result of 211:
And that will be the way each formula that is copied down will work!
What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!