Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Lookup Two Values In A List And Return Multiple Corresponding Values

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

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

  • #2
    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
    Kris

    ExcelFox

    Comment


    • #3
      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

      Comment


      • #4
        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
        Kris

        ExcelFox

        Comment


        • #5
          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, 19:16. Reason: Auto Merged Doublepost

          Comment


          • #6
            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?
            Kris

            ExcelFox

            Comment

            Trending

            Collapse

            There are no results that meet this criteria.

            Working...
            X