Create unique list based on specific criteria

Hi all - my first post...

I have an excel problem which is causing me brain ache...

I have a spreadsheet with a list of countries in column A. A1 contains the title "Destination" and A2:A120 contains the list of countries (e.g Botswana, Ecuador, Malaysia, Zimbabwe etc - there are 120 so far in alphabetical order). Column C contains their geographical region of the world. C1 has the heading "Region" and C2:C120 contains the corresponding region (e.g Africa, Latin America, Asia etc).

So, in column D I am trying generate a list of all African countries (D1 has the heading "Africa"). In column E I am trying to generate a list of all Latin American countires (E1 has the heading "Latin America") - and so on with the other columns and regions.

In cell D2 I have had some success using a reverse lookup formula, which provides the first African destination for the Region list (=INDEX($A$2:$A$120,MATCH($D$1,$C$2:$C$120,0)), but copying this down the colomn cells doesn't create a list of unique data entries; it just repeats "Botswana".

I have tried putting a feeder column in, but haven't found a formula that works yet and am totally stuck.

So - there you have it! How can I get the formula to provide the next country in the region (ie do the same reverse look up, but providing the next country afer the one previously given)?

Can anyone help?

Hi Deb

Thanks for your reply.

Your suggestion is the exact same that I have tried using, it still won't help me create the next (different) unique African country in the list.

Any advances on this formula woud lhelp...

Thanks

Hi - I have attached the file...thanks for your help.

"Now that's Magic" - Awesome!

Works a treat - thanks so much Fotis

Joynesy