3 Nested IFERROR Functions with VLOOKUP

Kronik

New Member
Joined
Jan 23, 2016
Messages
16
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),))
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,046
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 23, 2016
Messages
16
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

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,046
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 23, 2016
Messages
16

ADVERTISEMENT

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

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,046
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 23, 2016
Messages
16

ADVERTISEMENT

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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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)),"")
 
Last edited:

Kronik

New Member
Joined
Jan 23, 2016
Messages
16
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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 ????

??
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,108
Messages
5,599,767
Members
414,336
Latest member
Nicolas2465

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top