+ Reply to Thread
Results 1 to 14 of 14

Lookup a Value and Return Multiple Results

  1. #1
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    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).
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  2. #2
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019, MS 365
    Posts
    3,873

    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
    Cheers!
    Deep Dave

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    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).
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Registered User
    Join Date
    11-22-2013
    Location
    Islamabad
    MS-Off Ver
    Excel 2003
    Posts
    6

    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. #5
    Registered User
    Join Date
    04-11-2014
    Location
    Iowa
    MS-Off Ver
    Office 2013
    Posts
    20

    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. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Lookup a Value and Return Multiple Results

    Quote Originally Posted by SteelMaster View Post
    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?
    Don't understand what you're asking?

  7. #7
    Registered User
    Join Date
    04-11-2014
    Location
    Iowa
    MS-Off Ver
    Office 2013
    Posts
    20

    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. #8
    Registered User
    Join Date
    04-21-2014
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1

    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. #9
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    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. #10
    Registered User
    Join Date
    03-05-2014
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    6

    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?

    your help is greatly appreciated

  11. #11
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,430

    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.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,906

    Re: Lookup a Value and Return Multiple Results

    Jeff, I was just thinking the same thing myself
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  13. #13
    Registered User
    Join Date
    04-24-2014
    Location
    Canada
    MS-Off Ver
    Excel 2011
    Posts
    4

    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. #14
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    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))),"")

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Is it possible to lookup multiple values and return multiple rows of results?
    By justin11 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-20-2013, 12:02 PM
  2. [SOLVED] Name Variable LOOKUP to return multiple results
    By mmayna03 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-03-2012, 01:59 PM
  3. Lookup & return Multiple Results
    By benishiryo in forum Excel Tips
    Replies: 2
    Last Post: 07-11-2012, 10:11 AM
  4. How to return the maximum value in a multiple results LOOKUP
    By martinpe in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-11-2008, 11:39 PM
  5. Return Multiple Results with Lookup
    By Josh O. in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-04-2005, 05:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1