Hello all,
This tutorial is on how to return multiple results in Excel when looking up a value in a data set.
TL;DR Just give me the formula!
For those that simply want the formula, here you go:
=INDEX(ResultsColumn,SMALL(INDEX((CriteriaColumn=Criteria)*(ROW(CriteriaColumn)-ROW(FirstCellOfCriteriaColumn)+1),),COUNTIF(CriteriaColumn,"<>"&Criteria)+ROWS(A$1:A1)))
That was too fast. Quick, tell me what it means!
Quick Explanation:
The inner Index is creating an array of row numbers and 0's (the 0's are for where the criteria didn't match)
Small is being used to return the next row number that is not 0
The CountIf is being used to set the Small function's argument k so that it ignores 0's.
Using the row number returned from the Small function, the formula fetches the appropriate value from the ResultsColumn
Great, thanks!
Alright, anybody still reading is probably looking for a more in depth explanation of what is going on. First, let's set up an example table like the one provided here. This tutorial will assume that the table is in the range A1:B12 where row 1 is the header row.
Name Classification Apple Fruit Banana Fruit Broccoli Vegetable Cantaloupe Fruit Carrot Vegetable Date Fruit Elderberry Fruit Kale Vegetable Lettuce Vegetable Okra Vegetable Orange Fruit
The ResultsColumn (values we want to return) are the Names, located in cells A2:A12.
The criteria we'll be looking up is the classification, "Fruit" or "Vegetable", and the CriteriaColumn is located in cells B2:B12. For the purposes of this tutorial, the criteria will be "Fruit".
Here's a bit more in depth explanation about each of the formula's parts. When translating formulas, you should try to read the inner most bits first, and expand outward. This is how Excel interprets the formula, and so it's how we're going to as well.
=INDEX(ResultsColumn,SMALL(INDEX((CriteriaColumn=Criteria)*(ROW(CriteriaColumn)-ROW(FirstCellOfCriteriaColumn)+1),),COUNTIF(CriteriaColumn,"<>"&Criteria)+ROWS(A$1:A1)))
Extracting that we have:
INDEX((CriteriaColumn=Criteria)*(ROW(CriteriaColumn)-ROW(FirstCellOfCriteriaColumn)+1),)
The reason for the Index is so that the formula evaluates each cell individually.
The first part in this section to be calculated is:Technical side-note: Evaluating each cell individually is how Excel treats arrays. Without the Index function here, the formula would have to be confirmed with Ctrl+Shift+Enter instead of just Enter. This is what is known as array-entering a formula.
(CriteriaColumn=Criteria)
So using our table and criteria, that comes out to:
{"Fruit";"Fruit";"Vegetable";"Fruit";"Vegetable";"Fruit";"Fruit";"Vegetable";"Vegetable";"Vegetable";"Fruit"}="Fruit"
After the comparison, we are left with a list of boolean (True/False) values:
{True;True;False;True;False;True;True;False;False;False;True}
The next part in this section to be calculated is:
(ROW(CriteriaColumn)-ROW(FirstCellOfCriteriaColumn)+1)
The ROW(CriteriaColumn) happens first, and again (because of the Index), each cell will be evaluated individually, which provides us with the following array:
ROW(B2:B12) => ROW(B2;B3;B4;B5;B6;B7;B8;B9;B10;B11;B12)
The ROW function returns the row number of the cell that it is looking at, so basically just the numeric portion:
{2;3;4;5;6;7;8;9;10;11;12}
Now, we don't actually want our return values to start at the number 2, because then our results would be off by one cell when the outer Index function evaluates. It requires that the array of values being passed to it starts at the number 1, so we need to adjust it to do so. If you know the location of the data is never going to change, you can simply do a -1 here. I don't like to take that for granted though (the table might get cut/paste to a lower row, or there might be rows inserted above it, etc), so I like to subtract the row number of the first cell of data. That's the next part of this section we'll look at:
ROW(FirstCellOfCriteriaColumn)
The First cell of our criteria column is B2 and since we're only feeding that cell, we don't end up with an array, just a single value:
ROW(B2) => 2
Now that we have the values within this set of parentheses, it's time to add them together:
{2;3;4;5;6;7;8;9;10;11;12} - 2 + 1 =>
{0;1;2;3;4;5;6;7;8;9;10} + 1 =>
{1;2;3;4;5;6;7;8;9;10;11}
Then the two arrays get multiplied together. When boolean values have mathematical operations performed on them, they get converted to their integer counterparts. In other words, False becomes 0, and True becomes 1, so the calculation ends up looking like this:
{1;1;0;1;0;1;1;0;0;0;1} * {1;2;3;4;5;6;7;8;9;10;11} =>
{1;2;0;4;0;6;7;0;0;0;11}
You can see that where there was a True value (because the criteria was met), the corresponding number is returned, and where there was a False value (because the criteria was not met), a 0 is returned.
Alright, that section is complete and we have our array of positive numbers where the criteria was met. The next section we will evaluate is:
SMALL({1;2;0;4;0;6;7;0;0;0;11},COUNTIF(CriteriaColumn,"<>"&Criteria)+ROWS(A$1:A1))
The Small function will sort the array from smallest to largest:
{1;2;0;4;0;6;7;0;0;0;11} =>
{0;0;0;0;0;1;2;4;6;7;11}
Now that it is sorted, the second part needs to tell the Small function which of those values to return. We start this by using the CountIf to tell the formula how many values in the criteria column were not met:
COUNTIF(CriteriaColumn,"<>"&Criteria) =>
COUNTIF(B2:B2,"<>Fruit") =>
5
The 5th number in the sorted array is 0. It is the last 0 before the positive numbers:
{0;0;0;0;0;1;2;4;6;7;11}
To bump it up to the starting positive number, we add in a number of rows:
ROWS(A$1:A1)
The $ symbol means to use an absolute reference. So even when we copy the formula down, the first 1 will not change. After copying down the formula once, that will update to A$1:A2. ROWS, unlike ROW, cannot evaluate cells one at a time. So it simply returns however many rows were in the range that was fed to it:
First cell formula is in => ROWS(A$1:A1) => 1
First cell formula is in => ROWS(A$1:A2) => 2
First cell formula is in => ROWS(A$1:A3) => 3
etc
We'll just take that first one for now. Adding 1 to the 5 we got from the CountIf function gives us a value of 6. That means we want the 6th value in the Small array, which is the number 1:
{0;0;0;0;0;1;2;4;6;7;11}
So now all we have left is the outer index:
=INDEX(ResultsColumn,1) =>
=INDEX(A2:A12,1)
The first cell in the range of A2:A12 is A2. A2 is "Apple", so that is the output.
When we copy the formula down, we end up with:
=INDEX(A2:A12,2) => A3 => "Banana"
=INDEX(A2:A12,4) => A5 => "Cantaloupe"
Now why did it go from 2 to 4 you ask? Remember, we are returning the numbers from the SMALL array:
{0;0;0;0;0;1;2;4;6;7;11}
The 3rd non-zero value in that array is the number 4!
So that is how you can return multiple values by copying the same formula down.
If you want to return them by copying the formula across (return the results in a row instead of a column), use COLUMNS($A1:A1) instead of ROWS(A$1:A1).
If you copy the formula down too much, you'll find you get errors. This is because the Small array ran out of numbers to feed to the outer Index function. If you are on Excel 2007, you can wrap the formula in an IfError, like so (You can set "Error Message" to "" if you just want it to return blank):
=IFERROR(formula,"Error Message")
For earlier versions of Excel, it is a bit more long winded. You would use the Countif function again and compare it to the number of rows, so it would look like this:
=IF(ROWS(A$1:A1)>COUNTIF(CriteriaColumn,Criteria),"Error Message",formula).
I personally prefer the second form of error checking because it is backwards compatible.
Lastly, you can replace the Criteria with a cell reference so that you can update a single cell to return different results based on the criteria you typed in. For additional fanciness, you could set up a drop-down list using data validation so that you can choose the criteria from a list.
So, using cell E1 as the cell containing the Criteria of "Fruit" or "Vegetable", the complete formula would look like this:
=IF(ROWS(A$1:A1)>COUNTIF($B$2:$B$12,$E$1),"",INDEX($A$2:$A$12,SMALL(INDEX(($B$2:$B$12=$E$1)*(ROW($B$2:$B$12)-ROW($B$2)+1),),COUNTIF($B$2:$B$12,"<>"&$E$1)+ROWS(A$1:A1))))
I have attached an example workbook for your reference containing the table and the formula (both the vertical - copy down - and horizontal - copy across - versions).
Bookmarks