Extension to an existing formula.

**Icehockey44** · 12-13-2012, 07:19 AM

Hi All,

I have the following formula, which basically looks up the name and plots the different lines into certain cells on another spreadsheet.

what I would like it now to do, is to miss out any jobs where it is indicated in the Next Service Type as a Mini.

I have attached an example of the sheet for you to see what i'm needing.

thanks in advanc efor your help

Cheers

donna

**Bob Phillips** · 12-13-2012, 07:32 AM

Try

=IFERROR(INDEX('Job List'!$A$1:$G$24,SMALL(IF(('Job List'!$A$1:$A$24=A3)*('Job List'!$I$1:$I$24<>"MINI"),ROW('Job List'!$A$1:$A$24)),ROW($1:$1)),7),"")

**Icehockey44** · 12-13-2012, 07:58 AM

Wow, Thanks very much Bob, and it shorter than my original formula!!

Cheers

Donna

**Bob Phillips** · 12-13-2012, 10:27 AM

Only because I used the new (to Excel 2007) function IFERROR rather than IF(ISERROR as that doesn't require repeating the test condition when there is no error