Return Values Based on Multiple Criteria

mmchaley 

First, I think your actually talking about the cell in Column R (not Q), because that's where I find this formula:

=IFERROR(INDEX(SubAnalysis,MATCH([@Subcontractor],SubAnalysis[Subcontractor],0),MATCH(@OFFSET(DetailEst[[#Headers],[CLIN]],MATCH(LOOKUP(2,1/(DetailEstimate!$A$6:A9<>""),DetailEstimate!$A$6:A9),[CLIN],0),,COUNTIF([CLIN],LOOKUP(2,1/(DetailEstimate!$A$6:A9<>""),DetailEstimate!$A$6:A9))),SubAnalysis[#Headers],0)),"")

which is just far too complicated to decode. It may be fun to write a formula like this when you can get it to work--it is, I know from experience--but it loses some of the gloss when you try to go back a few months later to fix a problem. And for someone here in the techcommunity forum to try to make sense of it is, I would suggest, harder still, since we're not familiar with the underlying situation [not to take away from those very few who are able to do so]. Most text references on Excel, maybe all, recommend against such lengthy formulas for precisely this reason.

So, a suggestion: is it at all possible for you to break this apart into several different formulas (off to the side, probably on the SubAnalysis sheet itself)? We'll often recommend "helper columns" for this kind of purpose; in your case, the reference would be "helper cells"--but the idea is to take each INDEX/MATCH combo and do it in a cell of its own; similarly with the LOOKUP function; and so forth. Then a single formula that takes the results of those individual (and comprehensible) calculations could be used in cell R9. It would be easier for you to comprehend and debug too.