Need excel formula to vlookup and give multiple results

[tonyjyoo]

I have a sheet with data in columns A:D.

- Column D has i.e. state names
- Column A has i.e. cities

Is there a way to have a formula where it gives me say if I pick "california" in Column D, it spits out something like "Los Angeles, San Francisco, Irvine.....etc" ?

 

[DRSteele]

There are many ways. The simplest is to use the new function FILTER (available in Excel 365) to extract and list the records according to a specified criteria. You can also use function TEXTJOIN to extract the records into one cell.

This example is a very long list, but I showed just several records.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

Book1
	A	B	C	D	E	F	G	H	I
1
2	Rank (2016)	Municipality	Province	Population (2016)
3	1	Toronto	Ontario	2,731,571			Criteria	Municipality
4	2	Montreal	Quebec	1,704,694			Alberta	Calgary	Calgary, Edmonton, Red Deer, Strathcona County, Lethbridge, Wood Buffalo, St. Albert, Medicine Hat, Grande Prairie, Airdrie
5	3	Calgary	Alberta	1,239,220				Edmonton
6	4	Ottawa	Ontario	934,243				Red Deer
7	5	Edmonton	Alberta	932,546				Strathcona County
8	6	Mississauga	Ontario	721,599				Lethbridge
9	7	Winnipeg	Manitoba	705,224				Wood Buffalo
10	8	Vancouver	British Columbia	631,486				St. Albert
11	9	Brampton	Ontario	593,638				Medicine Hat
12	10	Hamilton	Ontario	536,917				Grande Prairie
13	11	Quebec City	Quebec	531,902				Airdrie
Sheet15

Cell Formulas
Range	Formula
H4	=FILTER(T_Pop[Municipality],T_Pop[Province]=G4)
I4	=TEXTJOIN(", ",,IF(G4=T_Pop[Province],T_Pop[Municipality],""))

<strike></strike>
[/FONT]

 

[tonyjyoo]

There are many ways. The simplest is to use the new function FILTER (available in Excel 365) to extract and list the records according to a specified criteria. You can also use function TEXTJOIN to extract the records into one cell.

This example is a very long list, but I showed just several records.

	A	B	C	D	E	F	G	H	I
1
2	Rank (2016)	Municipality	Province	Population (2016)
3	1	Toronto	Ontario	2,731,571			Criteria	Municipality
4	2	Montreal	Quebec	1,704,694			Alberta	Calgary	Calgary, Edmonton, Red Deer, Strathcona County, Lethbridge, Wood Buffalo, St. Albert, Medicine Hat, Grande Prairie, Airdrie
5	3	Calgary	Alberta	1,239,220				Edmonton
6	4	Ottawa	Ontario	934,243				Red Deer
7	5	Edmonton	Alberta	932,546				Strathcona County
8	6	Mississauga	Ontario	721,599				Lethbridge
9	7	Winnipeg	Manitoba	705,224				Wood Buffalo
10	8	Vancouver	British Columbia	631,486				St. Albert
11	9	Brampton	Ontario	593,638				Medicine Hat
12	10	Hamilton	Ontario	536,917				Grande Prairie
13	11	Quebec City	Quebec	531,902				Airdrie

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet15​

Worksheet Formulas Cell Formula H4 =FILTER(T_Pop[Municipality],T_Pop[Province]=G4) I4 =TEXTJOIN(", ",,IF(G4=T_Pop[Province],T_Pop[Municipality],"")) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

<strike></strike>

I don't have Excel 365.

I tried using the textjoin function but it didn't work. What is the T_Pop part of your formula?

 

[DRSteele]

The ExcelTable containing the data is called T_Pop. Try invoking the TEXTJOIN formula with Ctrl+Shift+Enter, since it contains an array. If TEXTJOIN doesn't work, your Excel version is old.

 

[tonyjyoo]

Doesn't work.