3 Nested IFERROR Functions with VLOOKUP

[Kronik]

I am getting an error of ' you have entered too many arguments' for this function. Can you please advise what i am doing wrong?

Thanks in advance:

=IFERROR(m2=130,VLOOKUP(J2,Mapping!A:C,3,FALSE), IFERROR(M2=1&D2="FZ",VLOOKUP(D2,Mapping!B:C,2,FALSE), iferror(vlookup(d2,Mapping!B:C,2,false),))

 

[StephenCrump]

Perhaps you can start by explaining the logic?

Presumably as a start If M2=130, you want VLOOKUP(J2,Mapping!A:C,3,FALSE)

Then what? What if M2 is not 130?

 

[Kronik]

So, if M2 is not 130, then go on to the next iferror, if not, then to the final vlookup.
in other words, the first iferror is an exception for code 130, then the second for code 1&FZ, and the final iferror is everything else.

The formula worked when i did this:
=IFERROR(VLOOKUP(J3,Mapping!A:C,3,FALSE),IFERROR(VLOOKUP(D3,Mapping!B:C,2,FALSE),)), but I had to add the exceptions for code 130 and 1&FZ, in bold below:

=IFERROR(m2=130,VLOOKUP(J2,Mapping!A:C,3,FALSE), IFERROR(M2=1&D2="FZ",VLOOKUP(D2,Mapping!B:C,2,FALSE), iferror(vlookup(d2,Mapping!B:C,2,false),))

 

[StephenCrump]

Sorry, still not clear. I am guessing that you want:

If M2=130 --> VLOOKUP(J2,Mapping!A:C,3,FALSE)

Else: If AND(M2=1,D2="FZ") you want VLOOKUP(D2,Mapping!B:C,2,FALSE)

which gives us:

=IF(M2=130,VLOOKUP(J2,Mapping!A:C,3,FALSE),IF(AND(M2=1,D2="FZ"),VLOOKUP(D2,Mapping!B:C,2,FALSE),"????"))

But then what? What is "????" if neither condition is satisfied?

At the moment, your formula adds another VLOOKUP(D2,Mapping!B:C,2,FALSE) which doesn't make sense.

 

[Kronik]

ok, so:

Code 130 needs to return the value from (J2,Mapping!A:C,3,FALSE), otherwise return value from (D2,Mapping!B:C,2,FALSE) - which is the last iferror and vlookup

That being said i probably need to add something like this:
M2=130&D2=CV needs to return the value from (J2,Mapping!A:C,3,FALSE), otherwise return value from (D2,Mapping!B:C,2,FALSE) - which is the last iferror and vlookup


Code 1&FZ - M2=1&D2=FZ needs to return the value from(D2,Mapping!B:C,2,FALSE), otherwise return value from the last if error and vlookup.

Sorry, these nested statements are new to me and someone actually gave me this to work with.

Thanks.

 

[StephenCrump]

Sorry, these nested statements are new to me and someone actually gave me this to work with.

No problem. Hence my suggestion that we focus on your logic, rather than getting tangled up in the unfamiliar formulae.

I think what you're saying is:

Condition 1
If cell M2 is 130, and cell D2 is "CV" then you want to return the value from VLOOKUP(J2,Mapping!A:C,3,FALSE)

If Condition 1 is not true then:
Condition 2:
You're describing this as: Code 1&FZ - M2=1&D2=FZ
I was guessing this meant: cell M2 is 1 and cell D2 is "FZ"?
If this condition is met, you want to return VLOOKUP(D2,Mapping!B:C,2,FALSE)

Condition 3:
= Condition 1 and Condition 2 both not true.
What do you want to return in this case? At the moment, it appears you are saying VLOOKUP(D2,Mapping!B:C,2,FALSE), i.e. the same as Condition 2?

But if you want the same value whether Condition 2 is true or not, then why test it?

 

[Kronik]

ok, you have the conditions correct. So here is my logic:

if code 130&CV or 1&FZ is met, return the value from the vlookup on condition 1 and 2 respectively.
if those are not met, then return the condition from the last vlookup.

The reason for that is, code 130&CV + 1&FZ has certain criteria that needs to be shown, but if that code is say 130&** or 1&** or anything else, say 200&**, then do the last vlookup.

The bug here fore me, is 130&CV + 1&FZ.

I hope this is clearer?

Thanks for your patience and understanding.

 

[Jonmo1]

Your 2nd and 3rd vlookups are identicle.
This renders your 2nd IF irrelevant (it will do the same vlookup regardless if the 2nd if is TRUE or FALSE)

Try
=IFERROR(IF(M2=130,VLOOKUP(J2,Mapping!A:C,3,FALSE),VLOOKUP(D2,Mapping!B:C,2,FALSE)),"")

 

[Kronik]

Thanks, but here is the problem:

your formula only looks at M2=130. On my last response, i tried to explain more of what the logic is, where:

if code 130&CV or 1&FZ is met, return the value from the vlookup on condition 1 and 2 respectively.(Per Stephen's last response)
if those are not met, then return the condition from the last vlookup.

The reason for that is, code 130&CV + 1&FZ has certain criteria that needs to be shown, but if that code is say 130&** or 1&** or anything else, say 200&**, then do the last vlookup.

The bug here fore me, is 130&CV + 1&FZ.

 

[Jonmo1]

So from what I can gather, you have 4 possible scenarios (2 cells that have 2 possible values each)..
Please fill in the ???

If M2 = 130 AND D2 = FZ, then I want ????
If M2 = 130 AND D2 = CV, then I want ????
If M2 = 1 AND D2 = FZ, then I want ????
If M2 = 1 AND D2 = CV, then I want ????

??