Iferror if and statement with Vlookup to continue through the rest of my formula if false

[jmenzel30]

Hello Everyone,

Hopefully this is one of last posts I'll put on here for this project. It has somehow turned into a monster that I can't stop trying to debug. Every time I add a rule, I have to spend hours trying to figure out why it won't let me use the formula, or why its giving me a #NA code or just leaves it blank. When I try to omit the iferror Statement on the nested formula, it won't work. When I put the iferror statement in, it will give me a blank answer. The working version that I have, creates inaccurate answers for scenarios.

Caution this is not for the faint of heart.

Currently My ideal formula on the MEC tab in cell D27 goes as follows:
IFERROR (IF ( VLOOKUP ( C5,'Alpha List'!C8 : C252 , 1 , FALSE ) = C 5 , " Prohibited Merchant " , " " ) , IFERROR ( IF ( AND ( VLOOKUP ( C 5 , ' Alpha List ' ! C307 : C308 , 1 , FALSE ) = C5 , ' Core Document Requirements ' ! W7 = " Yes " ) ," Prohibited Merchant " , " " ) ,IFERROR ( IF ( AND ( VLOOKUP ( C5 , ' Alpha List ' ! C302 : C303 , 1 , FALSE ) = C5 , ' Core Document Requirements ' ! W7="Yes") ," Prohibited Merchant " , " Concurrence Required " ) , IF ( C5 = 5941 , " Provide Additional Documents " , IFERROR ( IF ( VLOOKUP ( C5,'Alpha List'!C264:C299,1,FALSE ) = C5 , "Concurrence Required " , " " ) , IF ( AND ( G25 > 25000 , ' Core Document Requirements ' ! O11 = FALSE ) , " Concurrence Required " , IF ( AND ( ' Core Document Requirements ' ! M19 > 0 , ' Core Document Requirements ' ! M 19 < 500 , "Concurrence Required" , IF ( ' Core Document Requirements ' ! W7 = " Yes " , " Provide Additional Documents " , IF ( E17 > = 40 % , " Provide Additional Documents " , IF ( 'Core Document Requirements ' ! M19 = 0 , "Provide Additional Documents" , IF ( AND ( O 25 > 0 ,O 25 > 50000 ) ," Provide Additional Documents " , IF ( AND ( O 25 > 0 , O 25 < 50000 ) , " Approved " , IFERROR ( IF ( AND ( O 25 = 0 , G 25 > 50000 ) ,"Provide Additional Documents" , "Approved" ) , "Approved" ) ) ) ) ) ) ) , "Approved" ) ) ) ) ) ) )

This is giving me a blank answer. I have been playing with this thing for 2 days and I can’t get it to do EXACTLY what I want it do. I can just get it close. The rules that are inserted into this decision making device go as follows step by step

IFERROR (IF ( VLOOKUP ( C5,'Alpha List'!C8 : C252 , 1 , FALSE ) = C 5 , " Prohibited Merchant " , " " )

-IF FALSE, After remainder of formula its approved.

IFERROR ( IF ( AND ( VLOOKUP ( C 5 , ' Alpha List ' ! C307 : C308 , 1 , FALSE ) = C5 , ' Core Document Requirements ' ! W7 = " Yes " ) ," Prohibited Merchant " , " " )
-IF FALSE, After remainder of formula its approved.

IFERROR ( IF ( AND ( VLOOKUP ( C5 , ' Alpha List ' ! C302 : C303 , 1 , FALSE ) = C5 , ' Core Document Requirements ' ! W7="Yes") ,"
Prohibited Merchant " , " Concurrence Required " )
-IF FALSE, After remainder of formula it results in concurrence Required

IF ( C5 = 5941 , " Provide Additional Documents "
-IF FALSE, After remainder of formula its approved.

IFERROR ( IF ( VLOOKUP ( C5,'Alpha List'!C264:C299,1,FALSE ) = C5 , "Concurrence Required " , " " )
-IF FALSE After remainder of formula its approved.

IF ( AND ( G25 > 25000 , ' Core Document Requirements ' ! O11 = FALSE ) , " Concurrence Required "
-IF FALSE After remainder of formula its approved.

IF ( AND ( ' Core Document Requirements ' ! M19 > 0 , ' Core Document Requirements ' ! M 19 < 500 , "Concurrence Required" ,
-IF FALSE After remainder of formula its approved.

IF ( ' Core Document Requirements ' ! W7 = " Yes " , " Provide Additional Documents "
-IF FALSE After remainder of formula its approved

IF ( E17 > = 40 % , " Provide Additional Documents " ,
-IF FALSE After remainder of formula its approved

IF ( 'Core Document Requirements ' ! M19 = 0 , "Provide Additional Documents"
-IF FALSE After remainder of formula its approved

IF ( AND ( O 25 > 0 ,O 25 > 50000 ) ," Provide Additional Documents "
-IF FALSE After remainder of formula its approved

IF ( AND ( O 25 > 0 , O 25 < 50000 ) , " Approved "

IFERROR ( IF ( AND ( O 25 = 0 , G 25 > 50000 ) ,"Provide Additional Documents" , "Approved"

I’m attaching the document on my next post. Any help in finishing the remainder of this formula and making it work would help save me a headache. I still have other questions on the other pages, but this one kind of makes or breaks the entire sheet.

Thank you in advance.

[jmenzel30]

Here is the file for reference.

[jmenzel30]

I'm just trying to figure out how to tell an Iferror(If(and statement to move on to the next part of the formula if there is an error.

[JeteMc]

It is hard to trouble shoot this, at least for me, due to the ' Alpha List ' sheet being hidden and I assume protected. If it is possible to unprotect and/or unhide that sheet it may help.
As it is now cell D27 displays "Concurrence Required" which I assume in incorrect. It would help to know what D27 should display.
With a formula as complex as what is in cell D27 I would suggest that you break it down into multiple simple IF based formulas perhaps in column XFD.
Let us know if you have any questions.