Multiple value occurence index/match

**mechen8** · 01-05-2005, 04:46 PM

I'm having trouble pinning down the right formula to generate a report drawing from the following hypothetical data. This is how the data is arranged

City Country Size Category
Berlin Germany 2
L.A. USA 3
S.F. USA 2
Madrid Spain 2
Pitt USA 1

The report I'm trying to generate lists the cities and its size by country. The USA report will look like.

USA
City Size Category
L.A. 3
S.F. 2
Pitt 1

I need this report to be generated dynamically. Essentially if the country is designated as USA the report will pull the city name and size category. I am able to generate the first line of the report with the following formulas: =INDEX(A2:C6,MATCH("usa",B2:B6,0),1) and =INDEX(A2:C6,MATCH("usa",B2:B6,0),3)

The problem is that I can't figure out how to complete the report because the value USA has multiple occurences. Copying the above formula repeatedly returns the first line of the report "L.A." "3" because it is the first occurence of value USA. How do I return the the series of next occurences of value USA? (that will report "S.F." "2" and "Pitt" "1")

Thanks for the help.

**JVJNGO** · 08-01-2012, 01:34 AM

I have the almost the same problem.
Whenever you get your answer, can you forward to me. Thanks

**vlady** · 08-01-2012, 01:44 AM

Hello JVJNGO
if you can start another thread and post a sample workbook there and with what you expect to be the outcome, i'm sure someone could lend you a hand.

thanks.