How to get get cell valure based on parameters


New Member
Sorry if the title didn't say it all.

I have a probelm which I can't seem to solve.

I have a list called "DATA" and in column A I have countries (Sweden, USA, France and so on) and in column B I have Citys for these countries (For USA It could be New York, Miami and so on) and in column C I have street names in this cities. Each country, city and streetname can appear several times in the list. If column A is USA the city in column B will alway be a US city and same goes for column C (will be a steet in the city in column B in the country in column A).

I then need to create another list base on the "DATA" list (in another sheet). This list needs to look in "DATA" list in column A and write in all unique entries (all countires in the list)
Like this:


Column B need to list all the unique entries in column B for the value in B1 (USA in this case which is liked to A2 of cause)
Like this:

New York
Las Vegas

Column C need to list all the unique entries in column C for the value in C1 (New York in this case which is liked to B2 of cause)
New York
Ann street
Fulton street
Perry street

Is this possible without VB programming? This would help me massivly!!



Well-known Member

is this what you mean?....

Sample data....

Excel Workbook
2EnglandLondonLon Street
3USANew YorkNew Street
4FranceParisPar Street
5CanadaTorontoTor Street
6EnglandManchesterMan Street
7USAMiamiMia Street
8FranceNiceNic Street
9CanadaOttawaOtt Street
10EnglandOxfordOxf Street
11USALas VegasLas Street
12FranceCalaisCal Street
13CanadaVancouverVan Street
14EnglandLondonLon Street1
15USANew YorkNew Street1
16FranceParisPar Street1
17CanadaTorontoTor Street1
18EnglandManchesterMan Street1
19USAMiamiMia Street1
20FranceNiceNic Street1
21CanadaOttawaOtt Street1
22EnglandOxfordOxf Street1
23USALas VegasLas Street1
24FranceCalaisCal Street1
25CanadaVancouverVan Street1

Example results.....

Excel Workbook
2 Lon Street
3USAManchesterLon Street1

You will need to change the sheet name and cell ranges to suit your layout.
The formula In A2 needs entering with ctrl shift enter NOT just enter, you can then copy it down.
The formula In B2 needs entering with ctrl shift enter NOT just enter, you can then copy it across and down.

This solution was taken from here and slightly altered to suit......
Unique list to be created from a column where an adjacent column has text cell values | Get Digital Help - Microsoft Excel resource

I hope that helps.

Good luck.


Some videos you may like

This Week's Hot Topics