Returning multiples rows and columns using Vlookup or any other function

[AdibKhaldi]

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:

[TABLE="width: 682"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]Category[/TD]
[TD="align: center"]Client Number[/TD]
[TD="align: center"]City[/TD]
[TD="align: center"]Postal Code[/TD]
[TD="align: center"]Province[/TD]
[TD="align: center"] Office[/TD]
[TD="align: center"]#[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]11334455[/TD]
[TD="align: center"]HALIFAX[/TD]
[TD="align: center"]C0A1K2[/TD]
[TD="align: center"]NS[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]11445566[/TD]
[TD="align: center"]HALIFAX[/TD]
[TD="align: center"]B3C7V10[/TD]
[TD="align: center"]NS[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]11556677[/TD]
[TD="align: center"]MONTREAL[/TD]
[TD="align: center"]C0A1K3[/TD]
[TD="align: center"]QC[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]11667788[/TD]
[TD="align: center"]MONTREAL[/TD]
[TD="align: center"]B3C7V11[/TD]
[TD="align: center"]QC[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]11778899[/TD]
[TD="align: center"]MONTREAL[/TD]
[TD="align: center"]C0A1K4[/TD]
[TD="align: center"]QC[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]11890010[/TD]
[TD="align: center"]TORONTO[/TD]
[TD="align: center"]B3C7V12[/TD]
[TD="align: center"]ON[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]12001121[/TD]
[TD="align: center"]TORONTO[/TD]
[TD="align: center"]C0A1K5[/TD]
[TD="align: center"]ON[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]12112232[/TD]
[TD="align: center"]VANCOUVER[/TD]
[TD="align: center"]B3C7V13[/TD]
[TD="align: center"]BC[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]12223343[/TD]
[TD="align: center"]VANCOUVER[/TD]
[TD="align: center"]C0A1K6[/TD]
[TD="align: center"]BC[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD="align: center"]Category[/TD]
[TD="align: center"]Client Number[/TD]
[TD="align: center"]City[/TD]
[TD="align: center"]Postal Code[/TD]
[TD="align: center"]Province[/TD]
[TD="align: center"] Office[/TD]
[TD="align: center"]#[/TD]
[/TR]
[TR]
[TD]Return here:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

 

[dUBBINS]

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.

 

[AdibKhaldi]

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 :/