+ Reply to Thread
Results 1 to 5 of 5

Index lookup with 2 lookup values and then return multiple results - array formula

  1. #1
    Forum Contributor
    Join Date
    03-14-2013
    Location
    England
    MS-Off Ver
    2007
    Posts
    185

    Index lookup with 2 lookup values and then return multiple results - array formula

    Hi,

    Please look at the small example I created. I have btw left error handling out deliberately in this example as just focusing purely on the point that I require help with here.

    There are 2 dropdown boxes where you can select 1 of 2 values in each. I want to use an array formula to return a list of all the refs in column B where the values chosen in the 2 dropdown boxes match in columns C & D.

    I can find them all based on 1 value so far like this:

    {=INDEX($B$3:$D$12, SMALL(IF($G$2=$C$3:$C$12, ROW($C$3:$C$12)-MIN(ROW($C$3:$C$12))+1, ""), ROW(1:1)),1)}

    I need to add to this to take the other dropdown box into account as well, I have tried with the AND function like this:

    {=INDEX($B$3:$D$12, SMALL(IF(AND($G$2=$C$3:$C$12,$G$3=$D$3:$D$12), ROW($C$3:$C$12)-MIN(ROW($C$3:$C$12))+1, ""), ROW(1:1)),1)}

    But clearly I am missing something here as this returns a #VALUE error.

    Please show me the correct way to do this.

    Many thanks
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor nigelbloomy's Avatar
    Join Date
    11-06-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    1,011

    Re: Index lookup with 2 lookup values and then return multiple results - array formula

    Attachment 395899
    This formula worked for me in cell F8. Entered as an array
    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    03-14-2013
    Location
    England
    MS-Off Ver
    2007
    Posts
    185

    Re: Index lookup with 2 lookup values and then return multiple results - array formula

    Thought it needed a different approach, very helpful thank you

  4. #4
    Valued Forum Contributor nigelbloomy's Avatar
    Join Date
    11-06-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    1,011

    Re: Index lookup with 2 lookup values and then return multiple results - array formula

    This formula also works as an array. It's probably a little more beautiful because it doesn't just smash the columns together like the first formula did. It evaluates each column and then returns the rows where both criteria match. I learned this format from daffodil11.

    Please Login or Register  to view this content.

  5. #5
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Index lookup with 2 lookup values and then return multiple results - array formula

    All you really need is...
    Please Login or Register  to view this content.

+ 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. Replies: 3
    Last Post: 06-25-2014, 02:40 PM
  2. [SOLVED] Lookup to return multiple results - without array
    By ZeDoctor in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-22-2012, 07:42 AM
  3. [SOLVED] lookup multiple values of a single cell and return sum of results
    By Zeppelin17 in forum Excel General
    Replies: 7
    Last Post: 08-10-2011, 07:09 PM
  4. lookup 2 values and return multiple results
    By humboldtguy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-17-2010, 09:49 PM
  5. Array Lookup, Return/Sum Multiple Values
    By mre2000 in forum Excel General
    Replies: 3
    Last Post: 01-18-2010, 10:45 PM

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