Vlookup to provide multiple results for 1 option

[Hutchcove]

Hi all, Newbie with Excel and looking to create a dispatch database. I was told Vlookup was the function of choice. See table below:
What I want to do is have a 3 colums to the left of this hidden data with CITY, PRIMARY & 2NDARY as headers. A1 - City & Then B1 & C1 will provide the details from F1 & G1

I'm assuming this is simple but I can't seem to grasp the Vlookup tool. For all I know there is a better tool. All I do know is that using any VB coding is out of scope for me. Hoping this can be done with a couple of formulas.

Any assistance would be greatly appreciated.

E F G
CITY PRIMARY 2NDARY
[TABLE="width: 349"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Alma[/TD]
[TD]Progitech[/TD]
[TD]None[/TD]
[/TR]
[TR]
[TD]Amos[/TD]
[TD]Progitech[/TD]
[TD]None[/TD]
[/TR]
[TR]
[TD]Ancienne-Lorette[/TD]
[TD]Elias[/TD]
[TD]Microage QC[/TD]
[/TR]
[TR]
[TD]Ancienne-Lorette[/TD]
[TD]Elias[/TD]
[TD]Microage QC[/TD]
[/TR]
[TR]
[TD]Ange-Gardien[/TD]
[TD]Gilberto[/TD]
[TD]Jason[/TD]
[/TR]
[TR]
[TD]Anjou[/TD]
[TD]New Hire[/TD]
[TD]Jason[/TD]
[/TR]
[TR]
[TD]Anjou[/TD]
[TD]New Hire[/TD]
[TD]Jason[/TD]
[/TR]
[TR]
[TD]Asbestos[/TD]
[TD]Elias[/TD]
[TD]Progitech[/TD]
[/TR]
[TR]
[TD]Ascot-Corner[/TD]
[TD]Progitech[/TD]
[TD]Microage QC[/TD]
[/TR]
[TR]
[TD]Ayers Cliff[/TD]
[TD]Gilberto[/TD]
[TD]Progitech[/TD]
[/TR]
[TR]
[TD]Ayer's Cliff[/TD]
[TD]Gilberto[/TD]
[TD]Progitech[/TD]
[/TR]
[TR]
[TD]Ayer's Cliff[/TD]
[TD]Gilberto[/TD]
[TD]Progitech[/TD]
[/TR]
[TR]
[TD]Baie Comeau[/TD]
[TD]Progitech[/TD]
[TD]None[/TD]
[/TR]
[TR]
[TD]Baie Comeau[/TD]
[TD]Progitech[/TD]
[TD]None[/TD]
[/TR]
[TR]
[TD]Baie Comeau[/TD]
[TD]Progitech[/TD]
[TD]None[/TD]
[/TR]
[TR]
[TD]Baie Comeau[/TD]
[TD]Progitech[/TD]
[TD]None[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Try:

Book1
	A	B	C	D	E	F	G
1	City	Primary	2ndary		CITY	Primary	2ndary
2	Anjou	New Hire	Jason		Alma	Progitech	None
3	Amos	Progitech	None		Amos	Progitech	None
4	Baie Comeau	Progitech	None		Ancienne-Lorette	Elias	Microage QC
5					Ancienne-Lorette	Elias	Microage QC
6					Ange-Gardien	Gilberto	Jason
7					Anjou	New Hire	Jason
8					Anjou	New Hire	Jason
9					Asbestos	Elias	Progitech
10					Ascot-Corner	Progitech	Microage QC
11					Ayers Cliff	Gilberto	Progitech
12					Ayer's Cliff	Gilberto	Progitech
13					Ayer's Cliff	Gilberto	Progitech
14					Baie Comeau	Progitech	None
15					Baie Comeau	Progitech	None
16					Baie Comeau	Progitech	None
17					Baie Comeau	Progitech	None
Sheet5

Cell Formulas
Range	Formula
B2	=VLOOKUP($A2,$E$1:$G$17,MATCH(B$1,$E$1:$G$1,0))

Enter the formula in B2. Copy to C2. Then copy both of them down the column as far as needed.

 

[Hutchcove]

Thank you very much. This definitely did the trick. Didn't catch the concept of the Vlookup but not that I follow the formula with the table - makes perfect sense. A BIG THANK YOU


Try:

	A	B	C	D	E	F	G
City	Primary	2ndary	CITY	Primary	2ndary
Anjou	New Hire	Jason	Alma	Progitech	None
Amos	Progitech	None	Amos	Progitech	None
Baie Comeau	Progitech	None	Ancienne-Lorette	Elias	Microage QC
Ancienne-Lorette	Elias	Microage QC
Ange-Gardien	Gilberto	Jason
Anjou	New Hire	Jason
Anjou	New Hire	Jason
Asbestos	Elias	Progitech
Ascot-Corner	Progitech	Microage QC
Ayers Cliff	Gilberto	Progitech
Ayer's Cliff	Gilberto	Progitech
Ayer's Cliff	Gilberto	Progitech
Baie Comeau	Progitech	None
Baie Comeau	Progitech	None
Baie Comeau	Progitech	None
Baie Comeau	Progitech	None

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=VLOOKUP($A2,$E$1:$G$17,MATCH(B$1,$E$1:$G$1,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Enter the formula in B2. Copy to C2. Then copy both of them down the column as far as needed.[/QUOTE]