Various index match problems

**Joe Pineapples** · 05-03-2009, 03:54 AM

I know there are lots of versions of this problem on the web, but I'm not good enough at Excel to be able to change them to fit what I need,

On the attached workbook, on the "Progress Summary" page, cell C30 has a formula which should list the names of pupils who made 0 progress (as referenced by the Raw Data page, column R [progress] and column A [name].)

It only lists the first name and not all of them. Can anyone help me?

Joe P.

**zbor** · 05-03-2009, 04:38 AM

Easiest way will be to put Filter in Raw data 1st row and you can easily filter out in R column all that has 0... Is this OK?

**DonkeyOte** · 05-03-2009, 04:55 AM

If you want to use Arrays...

Progress Summary
A28: =COUNTIF('Raw Data'!R:R,B28)

C30: =IF(ROWS(C$30:C30)>$A$28,"",INDEX('Raw data'!$A$1:A$250,SMALL(IF('Raw data'!$R$1:$R$250=$B$28,ROW($1:$250)),ROWS(C$30:C30))))
committed with CTRL + SHIFT + ENTER

copy down to say C39

As you alter B28 you should find your list updates accordingly.

Arrays if over used will affect performance of the file.

**Joe Pineapples** · 05-03-2009, 06:47 AM

Thank you so much, guys!

Zbor - I considered your solution, but I am making a spreadsheet to analyse data for work and my colleagues need to be able to paste sheets in regularly and have the analysis done for them without any editing.

Your way works perfectly, but someone would bugger it up!

DonkeyOte - A perfect solution. Thank you very much. You are right about the overuse of arrays. I have 2007 at home, but as my company has 2003. I can't find any easy way around the lack of COUNTIFS in 2003 without arrays.

Thank you both again for your time and help.

Joe P.