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:

	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:

<tbody>
</tbody>

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