Forum Discussion
Having trouble with IF formula
I agree with mathetes​ that it's very difficult to diagnose such a formula. Comparing the if_true part of the formula with the if_false part I notice some inconsistencies concerning the number and placement of several parentheses. If you can fix that your problem will probably be solved.
You mention that the if_false calculation (i.e. X8 is not checked) returns the correct value, then why not us this:
= ( INDEX ( DATA_THK, MATCH(1,(Flg_Thk[Spec.]=N8)*(Flg_Thk[Series]=O8)*(Flg_Thk[Class]=P8)*(Size=Q8),0), MATCH(R8,FLG_TYPE, 0) ) + T8 * (R8="Lap Joint (LJ)") + V8 * (U8="RF") ) * IF(X8, 2, 1)I hope I got all the pairs of parentheses correct. See if this works. If not please upload you file or a link to on OneDrive or similar that give access to it. Then it will be a lot easier to help.
I'm sure this is all very clear to you (since you've created it), but do you realize that the image makes it quite difficult to figure out the cell references, on top of which the formula itself is next to unintelligible for those of us who are unfamiliar with the meaning behind the column headings.
Could I suggest (a) that you give us access to the actual workbook, not just an image; (b) help us help you by articulating in plain english what all those different conditions and consequences are referring to.
All of that having been said, I do notice that you've embedded some INDEX & MATCH functions in the IF conditions....that makes me think that you might find all of this more intelligible and manageable if you were to take those embedded lookups and calculations (which need to be performed before the IF...THEN can be completed)...take each of those calculations and make of each of them a "helper column" off to the right of what's displayed here. That way your IF can simply refer to operations already performed, rather than require them to be performed in the course of resolving the IF conditions and consequences.
I realize that there's a certain amount of personal pride in being able to write a deep single formula that does lots of things and produces the desired result ("Been there, done that" myself) but such a formula--yours is a perfect example--can also be VERY DIFFICULT to maintain down the road. There is something to be said for not trying too hard to be brilliant; keep it simple and take pride in the result's clarity and ease of explaining.
Hi Mathetes,
I didn't know where to get the link to upload, as I don't go into the OneDrive much, I forget it's there. I would've gladly uploaded the workbook if they had that option. Anyways it has been solved and cleaned up now.
I was actually trying to clean it up from the prior formula. It was a horror show.
