Adding if statement to existing formula

[conniehong]

Hi All,

I need your expertise. I have an existing formula (that I did not create) and I want to add in the statement that if cell C6 contains "One Off" that I want the formula to return blank. Can anyone help me out and incorporate this statement into the below formula?

Thanks in advance

=IF(ISERROR(VLOOKUP(SUBSTITUTE(SUBSTITUTE(CONCATENATE(H6,".",I6,".",J6,".",AP6,".",AU6,".",AQ6,".",AR6)," ","_"),"-","_"),Templates!O$24:P$47,2,FALSE))=TRUE,"",(VLOOKUP(SUBSTITUTE(SUBSTITUTE(CONCATENATE(H6,".",I6,".",J6,".",AP6,".",AU6,".",AQ6,".",AR6)," ","_"),"-","_"),Templates!O$24:P$47,2,FALSE)))

 

[steve the fish]

See if this works:

=IF(C6="one off","",IFERROR(VLOOKUP(SUBSTITUTE(SUBSTITUTE(CONCATENATE(H6,".",I6,".",J6,".",AP6,".",AU6,".",AQ6,".",AR6)," ","_"),"-","_"),Templates!O$24:P$47,2,FALSE),""))

 

[conniehong]

See if this works:

=IF(C6="one off","",IFERROR(VLOOKUP(SUBSTITUTE(SUBSTITUTE(CONCATENATE(H6,".",I6,".",J6,".",AP6,".",AU6,".",AQ6,".",AR6)," ","_"),"-","_"),Templates!O$24:P$47,2,FALSE),""))

Hi Steve,
Thanks for your help. However, this did not work. Error states... Circular Reference. Any other suggestions?

 

[steve the fish]

Have you placed it in C6? You cant do that. It needs to check C6.

 

[conniehong]

Have you placed it in C6? You cant do that. It needs to check C6.

No the original formula is on Cell AS6. That's where I pasted it.
At any rate, I found another solution. All the information that goes into my form A (where this formula was) get's pasted to another form (B). I was able to edit the formula on form B to incorporate this new if statement.

Thanks again for helping me. I really appreciate it.