Announcement

Collapse
No announcement yet.

Vlookup To Return Multiple Values

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Vlookup To Return Multiple Values



    Is there a way where i can vlookup a column and return all matches if there are multiple values?

  • #2
    Re: Vlookup To Return Multiple Values

    There was a thread on this just within the last couple of days or so.

    Please read the responses from Shg in this thread.

    Comment


    • #3
      Re: Vlookup To Return Multiple Values

      Please read the responses from Shg in this thread.
      Thanks for the plug, Six Strings. In that post, I provided a formula for looking up catenated cells in catenated columns -- not what the OP is looking for here, by my reading.

      I think the OP here is looking to return the 1st, 2nd, 3rd ... match of a VLOOKUP (or the equivalent). I expect it could be cobbled together by using INDEX and MATCH, using some kind of catenation with COUNTIF. I tried briefly, but couldn't finish it. A helper column would certainly work.

      adnanriaz, can you post an example?
      Entia non sunt multiplicanda sine necessitate.

      Comment


      • #4
        Re: Vlookup To Return Multiple Values

        Assuming that A2:B10 contains the lookup table, D2 contains the lookup value, and the corresponding values from Column B are to be returned, try the following...

        E2:

        =COUNTIF(A2:A10,D2)

        F2, copied down:

        =IF(ROWS(F$2:F2)<=$E$2,INDEX($A$2:$B$10,SMALL(IF($A$2:$A$10=$D$2,ROW($A$2:$A$10)-ROW($A$2)+1),ROWS(F$2:F2)),2),"")

        ...confirmed with CONTROL+SHIFT+ENTER. Note that the number 2 at the end of the formula determines the column from which to return the corresponding values. In this case, the corresponding values are to be returned from the second column or Column B. See the attached file.

        Hope this helps!
        Attached Files

        Comment


        • #5
          Re: Vlookup To Return Multiple Values

          I found this and it works great for what I am trying to do. However, I found that if my table information is on another sheet (which is where I need it to be) I can't get it to work. Any ideas?

          Comment


          • #6


            Re: Vlookup To Return Multiple Values

            tig08ger,

            Please do not post your question in threads started by others - - this is known as thread hijacking.
            Always start a new thread for *YOUR* question. If you find it helpful to clarify your needs you can include a link to this ( or any other ) thread.

            When starting a new thread be sure to give it a search-friendly title that aptly describes your need.
            AAE
            ----------------------------------------------------

            Forum Rules | Message to Cross Posters | How to use Tags

            Comment

            Working...
            X