Arrange data from table

**atlant15** · 12-21-2012, 11:22 AM

Hi,
I have a list with many details, and I would like to extract the details and sort them by rank.

as you can see in the attached file (this is only an example of the huge table I have), I have list of countries (column B), in each country I have sites (column C) that generates X revenues each (column D).
I already added a rank formula to arrange the order of revenues within the table (column A - I removed the actual formula in the example attached).

now I'm trying to have a list (as can be seen on the right) for each country arranged by rank (1,2,3...) with the name of the site and its revenues.
I struggled with Vlookup , If's , Sumproduct formulas, and others, and I can't find the solution.

your assistance is much appreciated
thanks.

**Fotis1991** · 12-21-2012, 11:36 AM

For the first table, in g2 and copy down and across this Array(control+shift+enter-not just enter) formula.

=IFERROR(INDEX(C$2:C$10;SMALL(IF($B$2:$B$10=$F$1;ROW(C$2:C$10)-1);ROW(C1)));"")

If you need change all semi colons in my formula to comma.

**benishiryo** · 12-21-2012, 11:39 AM

hi atlant15, you could use this non-array formula:
=INDEX(C$2:C$10,MATCH($F2&$F$1,INDEX($A$2:$A$10&$B$2:$B$10,),0))

but it has to be changed for every different countries. if you want something something that be applied to all, make sure that your actual data is really like this example because the formulas might be very sensitive to the columns & rows

**atlant15** · 12-21-2012, 11:48 AM

Wonderful, both great solutions.
thank you both

benishiryo - I didn't know you cac match 2 or even mor crateira by using the '&' sign like that - good to know.

thanks again.