If formula - too many functions for this argument error

[ashley72788]

Here is my formula, please help me find the error!

=iferror(F:F="", if(and(vlookup(Database!A:F,6,false)<=Delinquency!K:K, vlookup(Delinquency!K:K,Database!A:G,7,false)*Delinquency!S:S>=Delinquency!T:T), "X", ""), if(vlookup(Delinquency!F:F,CHCs!A:F,6,false)="x", if(and(vlookup(Delinquency!B:B,Database!A:F,6,false)<=Delinquency!K:K, vlookup(Delinquency!B:B,Database!A:G,7)*Delinquency!S:S>=Delinquency!T:T),"X",""),""),""),"")

Thank you!

[Pete_UK]

The first part doesn't make sense, i.e.:

F:F=""

Can you explain what you are trying to achieve?

Pete

[ashley72788]

Meaning if F:F is blank

[Pete_UK]

Do you mean if the whole of column F is blank, or just one cell?

It would need to be within some conditional statement, like:

=IF(F3="", ...

Also, I notice that your vlookup functions are not properly formed - the syntax is:

VLOOKUP(lookup_value, table, position, type)

but your first one is missing the lookup_value parameter (which is usually a single cell, like K3). VLOOKUP will return a single value (or an error), and so it cannot be compared directly with the column Delinquency!K:K. Your second VLOOKUP is using that column as the lookup_value, so it will fail there also, and then you are trying to multiply that by the column S:S in the Delinquency sheet (another fail), and so it goes on ...

Please try to explain what your formula is meant to be doing, or, better still, attach a sample workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Note that the Paperclip icon (Attachments button) does not work.

Hope this helps.

Pete

[ashley72788]

Hi There,

Here is an example - the formulas are in still in columns W & X.

I had to delete some information out for security reasons, but what you need is available

Thanks so much for helping, I really appreciate it!

[Pete_UK]

The formulae doesn't seem to do anything other than return a blank or an #N/A error, but if you want to wrap an IFERROR around it you need to do this:

=IFERROR(your_existing_formula,"")

so the formula in W2 would become:

=IFERROR(IF(F:F="", IF(AND(VLOOKUP(B:B,Database!A:E,5,FALSE)<=Delinquency!K:K, VLOOKUP(Delinquency!K:K,Database!A:G,7,FALSE)*Delinquency!S:S>=Delinquency!T:T), "X",""), IF(VLOOKUP(Delinquency!F:F,CHCs!A:E,5,FALSE)="x", IF(AND(VLOOKUP(Delinquency!B:B,Database!A:E,5,FALSE)<=Delinquency!K:K, VLOOKUP(Delinquency!B:B,Database!A:G,7)*Delinquency!S:S>=Delinquency!T:T),"X",""),"")),"")

and similarly for the formula in X2. When you copy the formula down, though, you just get blanks as a result.

Hope this helps.

Pete

[ashley72788]

This formula worked perfectly, the only thing I tried to change was adding if error to the formula. I don't want the cell to show "N/A", I would like it to be blank.

FORMULA BELOW WORKS

=IF(F:F="", IF(AND(VLOOKUP(B:B,Database!A:E,5,FALSE)<=Delinquency!K:K, VLOOKUP(Delinquency!K:K,Database!A:G,7,FALSE)*Delinquency!S:S>=Delinquency!T:T), "X",""), IF(VLOOKUP(Delinquency!F:F,CHCs!A:E,5,FALSE)="x", IF(AND(VLOOKUP(Delinquency!B:B,Database!A:E,5,FALSE)<=Delinquency!K:K, VLOOKUP(Delinquency!B:B,Database!A:G,7)*Delinquency!S:S>=Delinquency!T:T),"X",""),""))

THIS ONE DOES NOT

=iferror(F:F="", IF(AND(vlookup(Database!A:F,6,false)<=Delinquency!K:K, vlookup(Delinquency!K:K,Database!A:G,7,false)*Delinquency!S:S>=Delinquency!T:T), "X", ""), if(vlookup(Delinquency!F:F,CHCs!A:F,6,false)="x", if(and(vlookup(Delinquency!B:B,Database!A:F,6,false)<=Delinquency!K:K, vlookup(Delinquency!B:B,Database!A:G,7)*Delinquency!S:S>=Delinquency!T:T),"X",""),""),""),"")

[mrshl9898]

A sample workbook would help.

The problem with your statement is that IFERROR is formatted =IFERROR(value,value if error)

You have:

=iferror(

Value is

F:F="",

Value if error is

IF(AND(vlookup(Database!A:F,6,false)<=Delinquency!K:K, vlookup(Delinquency!K:K,Database!A:G,7,false)*Delinquency!S:S>=Delinquency!T:T), "X", "")

then this is after the statement, causing the issue

,IF(VLOOKUP(Delinquency!F:F,CHCs!A:F,6,FALSE)="x",IF(AND(VLOOKUP(Delinquency!B:B,Database!A:F,6,FALSE)<=Delinquency!K:K,VLOOKUP(Delinquency!B:B,Database!A:G,7)*Delinquency!S:S>=Delinquency!T:T),"X","")

If you want an IFERROR = "" to your original, then use

=IFERROR(IF(F:F="", IF(AND(VLOOKUP(B:B,Database!A:E,5,FALSE)<=Delinquency!K:K, VLOOKUP(Delinquency!K:K,Database!A:G,7,FALSE)*Delinquency!S:S>=Delinquency!T:T), "X",""), IF(VLOOKUP(Delinquency!F:F,CHCs!A:E,5,FALSE)="x", IF(AND(VLOOKUP(Delinquency!B:B,Database!A:E,5,FALSE)<=Delinquency!K:K, VLOOKUP(Delinquency!B:B,Database!A:G,7)*Delinquency!S:S>=Delinquency!T:T),"X",""),"")),"")

[mrshl9898]

It's hard to tell what is happening here. But referencing the whole column isn't correct.

Does this work in W2?

=IFERROR(IF(F2="", IF(AND(VLOOKUP(B2,Database!A:E,5,FALSE)<=K2, VLOOKUP(K2,Database!A:G,7,FALSE)*S2>=T2), "X",""), IF(VLOOKUP(F2,CHCs!A:E,5,FALSE)="x", IF(AND(VLOOKUP(B2,Database!A:E,5,FALSE)<=K2, VLOOKUP(B2,Database!A:G,7)*S2>=T2),"X",""),"")),"")

And in X2

=IFERROR(IF(F2="", IF(AND(VLOOKUP(B2,Database!A:F,6,FALSE)<=K2, VLOOKUP(K2,Database!A:G,7,FALSE)*S2>=T2), "X", ""), IF(VLOOKUP(F2,CHCs!A:F,6,FALSE)="x", IF(AND(VLOOKUP(B2,Database!A:F,6,FALSE)<=K2, VLOOKUP(B2,Database!A:G,7)*S2>=T2),"X",""),"")),"")

It's hard to tell without the Entity numbers... I inserted random ones and found some x values in columns W and X

[ashley72788]

It worked perfectly, you're a genius. Thank you SO much for your help. I wish I had your knowledge.