Results 1 to 3 of 3

Thread: Returning multiples rows and columns using Vlookup or any other function

  1. #1
    New Member
    Join Date
    Dec 2018
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Returning multiples rows and columns using Vlookup or any other function

    Hi,

    I have a data table to which I'm trying to do a vlookup to return all the columns of a matching lookup value, which I was able to do, but only one matching row would appear, while I want all matching rows (and not only the first match). Also, another issue I have is that a vlookup will only lookup the left most column while my lookup value is in the 3rd (City), so I suspect that Index+Match function would do? I couldn't paste a screenshot of the sheet, so here it is:

    Category Client Number City Postal Code Province Office #
    1 11334455 HALIFAX C0A1K2 NS 1 1
    2 11445566 HALIFAX B3C7V10 NS 2 1
    2 11556677 MONTREAL C0A1K3 QC 2 1
    3 11667788 MONTREAL B3C7V11 QC 3 1
    3 11778899 MONTREAL C0A1K4 QC 3 1
    4 11890010 TORONTO B3C7V12 ON 4 1
    4 12001121 TORONTO C0A1K5 ON 4 1
    5 12112232 VANCOUVER B3C7V13 BC 5 1
    5 12223343 VANCOUVER C0A1K6 BC 5 1

    Category Client Number City Postal Code Province Office #
    Return here:

    Given that I want it to return multiple columns, I created the following formula : {=VLOOKUP("HALIFAX", D2:H12, {1,2,3,4,5}, FALSE)}

    Also, if it would possible to have more than one lookup value, for example HALIFAX and MONTREAL, I don't know if a VBA code would be more appropriate in case no formula can execute it?

    Thank you very much!

    A.K

  2. #2
    Board Regular dUBBINS's Avatar
    Join Date
    Feb 2015
    Location
    Alaska
    Posts
    499
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Returning multiples rows and columns using Vlookup or any other function

    Google "Excel Advanced Filter Lists". That is probably the simplest way to handle what you are trying to do. There is a copy to another location option that might work for you.

  3. #3
    New Member
    Join Date
    Dec 2018
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Returning multiples rows and columns using Vlookup or any other function

    Thank you very much, I was looking exactly for this! I didn't know Excel has an advanced filtering option, which makes the task so much easier compared to creating unnecessarily long and complex formulas :/

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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