# Lookup a Value and Return Multiple Results

1. ## Lookup a Value and Return Multiple Results

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.
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.
The first part in this section to be calculated is:
(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).

2. ## Re: Lookup a Value and Return Multiple Results

Thats amazing..

Couldn't you have done that a week earlier? I spent almost a week understanding the same thing.. Haha!

That is perfectly explained though.

Cheers!

Deep

3. ## Re: Lookup a Value and Return Multiple Results

I was thinking about posting a method to do this exact same thing since it's frequently asked for in the Questions forums.

My suggested formula is a bit different than yours.

Here's an example for extracting TEXT entries that meet some condition.

Data in the range A2:B10...

``Please Login or Register  to view this content.``
We want to extract the names from column A that have a status of Passed in column B and return the results starting in cell D2 downwards.

Both formulas are array formulas**.

If using Excel 2007 or later enter this array formula** in D2 and copy down until you get blanks (meaning all the data that meets the condition has been extracted):

=IFERROR(INDEX(A:A,SMALL(IF(B\$2:B\$10="Passed",ROW(B\$2:B\$10)),ROWS(D\$2:D2))),"")

This next example will work in any version of Excel. Array entered** in D2 and copied down until you get blanks:

=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(A:A,SMALL(IF(B\$2:B\$10="Passed",ROW(B\$2:B\$10)),ROWS(D\$2:D2)))))

** Array formulas are entered differently than a regular formula. After you type in a regular formula you hit the ENTER key. With an array formula you *must* use a combination of keys. Those keys are the CTRL key, the SHIFT key and the ENTER key. That is, hold down both the CTRL key and the SHIFT key then hit the ENTER key.

When done properly Excel will enclose the formula in squiggly brackets { }. You can't just type these brackets in, you *must* use the key combo to produce them. Also, anytime you edit an array formula it *must* be re-entered as an array using the key combo.

Your results will look like this:

``Please Login or Register  to view this content.``
In the 2nd formula:

LOOKUP("zzzzz",CHOOSE({1,2},"",

Is an alternative and efficient error trap that can be used when the returned data is TEXT (as in this example).

4. ## Re: Lookup a Value and Return Multiple Results

Very nice indeed! I am really got important information's to this work. Thanks for posting!

5. ## Re: Lookup a Value and Return Multiple Results

Why does the last part of this formula use column D? And why are the results one row down from where it should be?

6. ## Re: Lookup a Value and Return Multiple Results

Originally Posted by SteelMaster
Why does the last part of this formula use column D?
Because that's where the results are going and we are just using the references to column D as an incrementer in the SMALL function. For example:

D2: SMALL(Array,ROWS(D\$2:D2)) = SMALL(Array,1)
D3: SMALL(Array,ROWS(D\$2:D3)) = SMALL(Array,2)
D4: SMALL(Array,ROWS(D\$2:D4)) = SMALL(Array,3)
D5: SMALL(Array,ROWS(D\$2:D5)) = SMALL(Array,4)
etc
etc

And why are the results one row down from where it should be?

7. ## Re: Lookup a Value and Return Multiple Results

I figured out the other part. I didn't use the whole column A:A. Duh...thx.

Thanks again!
Know anyone who could solve the post I asked about. I am desperate.
Thanks.

8. ## Re: Lookup a Value and Return Multiple Results

Hello, I just registered to the forum.
Tony, the formula work well with what I'm wanting to do, however I can seem to figure out how to make it work with multiple criteria.
For example if I want to return the items that are between 10.000 and 10.250

Criteria >=10.000 <=10.250
Item 1 10.0000
Item 2 10.1250
Item 3 10.5000

I've tried changing the formula to Countifs and adding the other criteria but it's not giving me the right results. Can you advise how the formula needs to be modified.
Thanks,

9. ## Re: Lookup a Value and Return Multiple Results

Try one of these...

Data Range
 A B C D E F G 1 ------ ------ ------ ------ ------ ------ ------ 2 Item 1 10.0000 10.000 10.250 Item 1 3 Item 2 10.1250 Item 2 4 Item 3 10.5000 5

This array formula** entered in G2 and copied down until you get blanks:

=IFERROR(INDEX(A:A,SMALL(IF(B\$2:B\$4>=D\$2,IF(B\$2:B\$4<=E\$2,ROW(B\$2:B\$4))),ROWS(G\$2:G2))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Or, if you're using Excel 2010 or later, this normally entered formula in G2 and copied down until you get blanks:

=IFERROR(INDEX(A:A,AGGREGATE(15,6,1/(1/((B\$2:B\$4>=D\$2)*(B\$2:B\$4<=E\$2)*ROW(B\$2:B\$4))),ROWS(G\$2:G2))),"")

The array entered formula is slightly faster to calculate.

10. ## Re: Lookup a Value and Return Multiple Results

hi there,

Thank you for such a great help on this formula.
one question though,
i'm using this formula in a roster scene to pick up people that call in sick and display their rostered job in a different cell.
the problem that i am having say for example employee one calls in sick at 2 pm and i assign that job in a corresponding cell to another,
then employee 2 calls in sick for the 1pm shift (the call was made after i have already rostered employee one a replacement), the formula automatically places the 1 pm in the cell above the 2pm.
is there a way to stop it from changing the value once a value is entered in a corresponding cell?

11. ## Re: Lookup a Value and Return Multiple Results

I have rescinded my warning here, please continue. This subforum is not for posting specific questions and more open, so the hijacking rule is not appropriate in this case.

12. ## Re: Lookup a Value and Return Multiple Results

Jeff, I was just thinking the same thing myself

13. ## Re: Lookup a Value and Return Multiple Results

Awsome, but I ran into the announced problem "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." How do you go about solving this problem?

Thanks a lot.

14. ## Re: Lookup a Value and Return Multiple Results

Wrap the formula inside the IFERROR function like this:

=IFERROR(INDEX(A:A,SMALL(IF(B\$2:B\$4>=D\$2,IF(B\$2:B\$4<=E\$2,ROW(B\$2:B\$4))),ROWS(G\$2:G2))),"")

There are currently 1 users browsing this thread. (0 members and 1 guests)