ability to get an N/A using sumifs formula|General Excel Questions & Answers|Excel Forum|My Online Training Hub

I am trying to look up a variable by driver number based on multiple criteria, therefore I was using a sumifs formula.  If the driver number doesn't exist in the source data, we want to look at the driver number minus 1 (and then minus 2 and minus 3) because of the way our system replaces drivers and re-numbers them.  Our original file was able to use a vlookup because it looked for only one criteria. In that instance we used the following ifna formula.

=IFNA(VLOOKUP(C2,'Advantage Driver Ind'!A:D,4,FALSE),(IFNA((VLOOKUP((LEFT(C2,10)&(RIGHT(C2,1)-1)),'Advantage Driver Ind'!A:D,4,FALSE)),(IFNA((VLOOKUP((LEFT(C2,10)&(RIGHT(C2,1)-2)),'Advantage Driver Ind'!A:D,4,FALSE)),(VLOOKUP((LEFT(C2,10)&(RIGHT(C2,1)-3)),'Advantage Driver Ind'!A:D,4,FALSE)))))))

Now, however, the indicator we want back (either a 0 or 1) is within multiple variables so we have to look it up by ID number (C2) AND description (Advantage Driver Ind).  I can't figure out how to get the sumifs to to keep the first value it finds because it's returning a 0 value even if it doesn't find that criteria.  This is the formula I have currently, however this one keeps looking until it finds a 1.  I need it to only keep looking if there is no value for that ID number (driver) and description.  

=IF(SUMIFS('Auto Driver Variables'!D:D,'Auto Driver Variables'!A:A,C1134,'Auto Driver Variables'!I:I,"ADVANTAGE DRIVER IND")=1,SUMIFS('Auto Driver Variables'!D:D,'Auto Driver Variables'!A:A,C1134,'Auto Driver Variables'!I:I,"ADVANTAGE DRIVER IND"),IF(SUMIFS('Auto Driver Variables'!D:D,'Auto Driver Variables'!A:A,(LEFT(C1134,10)&RIGHT(C1134,1)-1),'Auto Driver Variables'!I:I,"ADVANTAGE DRIVER IND")=1,SUMIFS('Auto Driver Variables'!D:D,'Auto Driver Variables'!A:A,(LEFT(C1134,10)&RIGHT(C1134,1)-1),'Auto Driver Variables'!I:I,"ADVANTAGE DRIVER IND"),IF(SUMIFS('Auto Driver Variables'!D:D,'Auto Driver Variables'!A:A,(LEFT(C1134,10)&RIGHT(C1134,1)-2),'Auto Driver Variables'!I:I,"ADVANTAGE DRIVER IND")=1,SUMIFS('Auto Driver Variables'!D:D,'Auto Driver Variables'!A:A,(LEFT(C1134,10)&RIGHT(C1134,1)-2),'Auto Driver Variables'!I:I,"ADVANTAGE DRIVER IND"),IF(SUMIFS('Auto Driver Variables'!D:D,'Auto Driver Variables'!A:A,(LEFT(C1134,10)&RIGHT(C1134,1)-3),'Auto Driver Variables'!I:I,"ADVANTAGE DRIVER IND")=1,SUMIFS('Auto Driver Variables'!D:D,'Auto Driver Variables'!A:A,(LEFT(C1134,10)&RIGHT(C1134,1)-1),'Auto Driver Variables'!I:I,"ADVANTAGE DRIVER IND"),0))))

So to recap, if there is a matching driver number and description, I need it to pull back the 0 or 1 from the source data, however, if there is no matching driver number and description, I need the formula to look for the original driver number minus 1, then if there is no match for that, minus 2, and then so on to minus 3.  Is there a way to do this with the sumifs formula or is there another option for querying by multiple variables that would work better?

Thanks so much for your help!