Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Lookup Two Values In A List And Return Multiple Corresponding Values

  1. #1
    Join Date
    26th March 2008
    Posts
    7

    Lookup Two Values In A List And Return Multiple Corresponding Values

    I know how to look up one value in a list and return multiple corresponding values, but I don't know how to change the formula to two values.

    My current code is:

    =IF(ISERROR(INDEX($A$1:$K$21,SMALL(IF($K$1:$K$21="QUO",ROW($K$1:$K$21)),ROW(1:1)),5)),"",INDEX($A$1:$K$21,SMALL(IF($K$1:$K$21="QUO",ROW($K$1:$K$21)),ROW(1:1)),5))

    I want it to also look up "Y" in column I; $I$1:$I$21="Y"

    Thanks.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: Lookup Two Values In A List And Return Multiple Corresponding Values

    Hi MadeleineB,

    Welcome to board!!

    In a spare cell(suppose L1),

    =SUMPRODUCT(--($K$1:$K$21="QUO"),--($I$1:$I$21="Y"))

    Now your formula would be,

    =IF(ROWS($M$1:$M1)<=$L$1,INDEX($E$1:$E$21,SMALL(IF($K$1:$K$21="QUO",IF($I$1:$I$21="Y",(ROW($K$1:$K$21))),ROWS($M$1:$M1))),"")

    Array entered. To Enter the array formula hold down Ctrl and Shift while pushing Enter.

    HTH

  3. #3
    Join Date
    26th March 2008
    Posts
    7

    Re: Lookup Two Values In A List And Return Multiple Corresponding Values

    Hi Krishna,

    Thanks for the welcome.

    I've tried the code and the first bit seamed to work it returned how many cases match my criteria.

    When I tried the second part I got a error message telling me that; 'You've entered too few argement for this function'

    Any ider how to fix it?

    Many thanks

    Madeleine

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: Lookup Two Values In A List And Return Multiple Corresponding Values

    Hi,

    It was an untested formula. There was an extra "(" before the ROW part.

    Try this

    =IF(ROWS($M$1:$M1)<=$L$1,INDEX($E$1:$E$21,SMALL(IF($K$1:$K$21="QUO",IF($I$1:$I$21="Y",ROW($K$1:$K$21))),ROWS($M$1:$M1))),"")

    HTH

  5. #5
    Join Date
    26th March 2008
    Posts
    7

    Re: Lookup Two Values In A List And Return Multiple Corresponding Values

    It's now returning the first value that matches the criteria in column K but not the second or third.

    It repeats the first value.
    Auto Merged Post Until 24 Hrs Passes;

    It works, I have repeated data in the columns and I did not realise.

    Thanks so much for you help Kris.
    Last edited by MadeleineB; April 2nd, 2008 at 19:16. Reason: Auto Merged Doublepost

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: Lookup Two Values In A List And Return Multiple Corresponding Values

    It should be dragged down. Not across.
    Anyway can you attach the workbook?

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 7
    Last Post: March 19th, 2008, 20:07
  2. Return Multiple Values From Lookup To Single Cell
    By jwhite68 in forum Excel General
    Replies: 8
    Last Post: February 27th, 2007, 21:42
  3. Return Multiple Values From Lookup Table
    By stevekirk in forum Excel General
    Replies: 3
    Last Post: January 5th, 2007, 22:27
  4. Lookup Single Value In One Array, and Return Multiple Values
    By Jkholzsager in forum Excel General
    Replies: 9
    Last Post: July 27th, 2006, 11:00
  5. Lookup function to return multiple values
    By mimminito in forum Excel General
    Replies: 2
    Last Post: March 20th, 2006, 18:35

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
  •  
porno