Hello. I am trying to modify this formula to do one more thing, but I can't quite figure out how to do it. I have part 1 and 2, but part 3 is giving me a very hard time. The formula is not working correctly. It is supposed to do several things.
- Do an HLookup for a row and return the last value in the row.
- If there is no value (blank), the value should be "Incomplete."
- If HLookup finds a value of "Fail," it should look to see if there is a value of "Pass" BEFORE (to the left of) "Fail." If this exists, then it should return a value of "OK."
Here is what I have so far.
=IF(ISNA(HLOOKUP(REPT("z",10),H26:Z26,1)),"Incomplete",HLOOKUP(REPT("z",10),H26:Z26,1)), IF(AND(HLOOKUP(REPT("z",10),H21:Z21,1)="Fail",HLOOKUP(REPT("z",10),H21:Z21,1)="Pass"),"OK","Fail")
Thanks in advance!