Help improving my formula

[lomerick]

I'm getting a !VALUE error here, can someone please help me fix my formula or simplify it. Any help would be greatly appreciated.

=IF(K4&S4<>"342Prestige","Not needed"),IF(Y4="","Input Imp SAP Box",IF(OR(Y4="N6P 420",Y4="L6P 430",AB4="ICBP",K4=805),"Not needed",IF(AND(Y4="ANP 430",S4="Prestige",F4>0),"ok",IF(AND(Y4="A6P 430",S4="Prestige",F4>0),"ok",IF(AND(X4="India",F4>0),"ok",IF(OR(Y4="A6P 430",Y4="ANP 430"),IFERROR(VLOOKUP(B4&X4,GOS!A:D,4,0),"Request"),IF(C4="A553",IFERROR(VLOOKUP(B4&9,'NOS Minus'!A:F,6,0),"Request"),IF(C4=5577,IFERROR(VLOOKUP(B4&5,'NOS Minus'!A:F,6,0),"Request"),IF(OR(Y4="F6P 430",Y4="F5P 420"),IFERROR(VLOOKUP(B4&K4,'NOS Minus'!A:F,6,0),"Request"))))))))))

 

[StephenCrump]

Hi ABSAIFI

Welcome to the Forum!

There's a button marked "+ Post New Thread" towards the bottom left of this page: Excel Questions

Click this, and you can post your own new thread.

 

[StephenCrump]

Please try this sample Start cell column K4 to AB4

It should POST "Request" but with the formula above it POST "Not Needed"

I assume you mean the data is in K25:AB25

This formula: =IF(OR(Y25="F6P 430",Y25="F5P 420"),IFERROR(VLOOKUP(B25&K25,'NOS minus'!A:F,6,0),"Request"))

will return:

1. If Y25="F6P 430" OR Y25="F5P 420" (True in this case) then ..

a) VLOOKUP(B25&K25,'NOS minus'!A:F,6,0) if the VLOOKUP does not produce an error

OR

b) "Request" if VLOOKUP produces an error

OR

2. If Y25<>"F6P 430" AND Y25<>"F5P 420": FALSE

I assume the VLOOKUP is not returning "Not needed". You can check this quite easily.

So if the formula in Post #7 is returning "Not needed", then some earlier condition is being satisfied. Post #4 showed you the conditions that will return "Not needed". I suggest you check that there are what you want?

 

[StephenCrump]

What I want to add is IF K4=342 and S4 is not equal to "Prestige"

to this formula:

=IF(Y4="","Input Imp SAP Box",IF(OR(Y4="N6P 420",Y4="L6P 430",AB4="ICBP",K4=805),"Not needed",IF(AND(Y4="ANP 430",S4="Prestige",F4>0),"ok",IF(AND(Y4="A6P 430",S4="Prestige",F4>0),"ok",IF(AND(X4="India",F4>0),"ok",IF(OR(Y4="A6P 430",Y4="ANP 430"),IFERROR(VLOOKUP(B4&X4,GOS!A:D,4,0),"Request"),IF(C4="A553",IFERROR(VLOOKUP(B4&9,'NOS Minus'!A:F,6,0),"Request"),IF(C4=5577,IFERROR(VLOOKUP(B4&5,'NOS Minus'!A:F,6,0),"Request"),IF(OR(Y4="F6P 430",Y4="F5P 420"),IFERROR(VLOOKUP(B4&K4,'NOS Minus'!A:F,6,0),"Request")))))))))) I even tried inserting it to:

<tbody>
</tbody>

IF(OR(Y4="N6P 420",Y4="L6P 430",AB4="ICBP",K4=805, K4&S4<>"342Prestige"),"Not needed

Or am I doing it wrong?

1. K4=342 and S4 <> "Prestige" is not the same as K4&S4<>"342Prestige"

2. Does your formula above (let's call this YourBigFormula) give you the results you want before you try to add the new condition?

3. How do you want to combine the conditions. Perhaps your formula will have this format:

=IF(AND(K4=342,S4 <> "Prestige"),YourBigFormula,SomethingElse)

Or maybe

=IF(AND(K4=342,S4 <> "Prestige"),SomethingElse, YourBigFormula)

where SomethingElse is perhaps another formula, or just a text value e.g. "Error: Try again"

 

[lomerick]

The above logic is correct. It's just not working on how I want it to. But thanks for the help. Tried different approach but no luck.

 

[lomerick]

=IF(AND(K4=342,S4 <> "Prestige"),YourBigFormula,SomethingElse)

"

This one worked. Downside is calculation takes longer but its ok.

Thanks a lot.

 

[StephenCrump]

Great, I am glad you got it working.