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!
Hello,
If you can upload a sample file with some dummy data along with a mockup of the expected result that would be of great help for all.
Br,
Anders
Which version of Excel do you have?
Attached is a sample file of the new formula and original formula. We are on Office 16. Thanks for your help!
Hello,
Do you want something like this? Below formula gives #N/A if no match is found, else it pulls the indicator value for that driver. Might be that you need to finish the formula with CTRL + SHIFT + ENTER for it to work. See attached copy, sheet Formulas (2).
=INDEX('Auto Driver Variables-new'!$C$2:$C$43,MATCH(1;('Auto Driver Variables-new'!$A$2:$A$43='Formulas'!A2)*('Auto Driver Variables-new'!$D$2:$D$43="ADVANTAGE DRIVER IND"),0))
Br,
Anders
Thank you for the response! I didn't even think of Index/Match (probably because I dislike that formula and usually avoid it at all costs). 🙂
