Differences in array between 2007 and 2010

[steve001]

Hi : I have a table with multiple rows and 5 columns . At home I use 2010 and have inserted an array to dislay a row with 4 columns based on one criteria in the table.

=INDEX(tbl,SMALL(IF((INDEX(tbl,,$D$3,1)=$D$2),ROW(tbl)-MIN(ROW(tbl))+1,""),ROWS(D6:$D$6)),,1)

Which seems to work - except for the th annoying #NUM! message if there's no matching value. Is there a way to remove that message to show a blank cell?

Also that same array when I use it at work on 2007 it copies the value of the criteria to all the cells. Obviously I'm missing something. Is it that there is a difference between 2007 and 2010 ? or is there an Excel option I haven't checked at work that I may have done at home?

Thanks

Steve

[Fotis1991]

=IFERROR(INDEX(tbl,SMALL(IF((INDEX(tbl,,$D$3,1)=$D$2),ROW(tbl)-MIN(ROW(tbl))+1,""),ROWS(D6:$D$6)),,1),"")

Do you confirm this formula as an ARRAY formula?

[:) Sixthsense :)]

Is there a way to remove that message to show a blank cell?

Cover the formula with iferror function like this

=IFERROR(INDEX(tbl,SMALL(IF((INDEX(tbl,,$D$3,1)=$D$2),ROW(tbl)-MIN(ROW(tbl))+1,""),ROWS(D6:$D$6)),,1),"")

when I use it at work on 2007 it copies the value of the criteria to all the cells.

I believe the file is kept in Manual Calculation Mode or the calculation process may not completed

[steve001]

Thanks that solved the NUM message! I still can't crack the issue of why the formula is returning the same value in each of the cells.