IF/VLOOKUP with #N/A results

[Supe]

I have a IF function that if a box is marked with an "x" then do a VLOOKUP
against a separate worksheet to get a shipment total. If there is no "x"
then that item isn't carried so it lists "Not Carried" in the cell. Issue is
if there is an "x", but there isn't a match in the sheet I am doing the
VLOOKUP in the results come back as #N/A. Would like that result to say "No
Shipments" instead of #N/A. This is beyond my Excel capabalities. The
formula I have so far is below.


=IF(AD3="x",VLOOKUP(A3,'[Access
Data.xlsx]Data_Qry'!$A$2:$B$555555,2,FALSE),"Not Carried")

 

[Per Jessen]

HI

Try this:

=IF(iserror(VLOOKUP(A3,'[Access
Data.xlsx]Data_Qry'!$A$2:$B$555555,2,FALSE),"No
Shipments",IF(AD3="x",VLOOKUP(A3,'[Access
Data.xlsx]Data_Qry'!$A$2:$B$555555,2,FALSE),"Not Carried")))

Regards,
Per

 

[Luke M]

We can add another IF arguement

=IF(AD3="x",IF(ISNA(VLOOKUP(A3,'[Access
Data.xlsx]Data_Qry'!$A$2:$B$555555,2,FALSE)),"No
Shipments",VLOOKUP(A3,'[Access
Data.xlsx]Data_Qry'!$A$2:$B$555555,2,FALSE),"Not Carried"))

I'm not running it myself, but I think in 2007 you could also use:
=IF(AD3="x",IFERROR(VLOOKUP(A3,'[Access
Data.xlsx]Data_Qry'!$A$2:$B$555555,2,FALSE),"Not Carried"),"No Shipments")

 

[Bernard Liengme]

The answer from Per is just fine but if you are using Excel 2007 you can use
a simpler formula

=IFERROR(IF(AD3="x",VLOOKUP(A3,'[Access
Data.xlsx]Data_Qry'$A$2:$B$555555,2,FALSE),"Not Carried"), "No shipments")

or (easier to follow)
=IF(AD3<>"x","Not carried", IFERROR(VLOOKUP(A3,'[Access
Data.xlsx]Data_Qry'$A$2:$B$555555,2,FALSE), "No shipments")

Note you need only enter your formula once.
best wishes

 

[Supe]

I tried entering this formula and I get a message saying "You've entered too
many arguments for this function".

HI

Try this:

=IF(iserror(VLOOKUP(A3,'[Access
Data.xlsx]Data_Qry'!$A$2:$B$555555,2,FALSE),"No
Shipments",IF(AD3="x",VLOOKUP(A3,'[Access
Data.xlsx]Data_Qry'!$A$2:$B$555555,2,FALSE),"Not Carried")))

Regards,
Per

I have a IF function that if a box is marked with an "x" then do a VLOOKUP
against a separate worksheet to get a shipment total. If there is no "x"
then that item isn't carried so it lists "Not Carried" in the cell. Issue
is
if there is an "x", but there isn't a match in the sheet I am doing the
VLOOKUP in the results come back as #N/A. Would like that result to say
"No
Shipments" instead of #N/A. This is beyond my Excel capabalities. The
formula I have so far is below.


=IF(AD3="x",VLOOKUP(A3,'[Access
Data.xlsx]Data_Qry'!$A$2:$B$555555,2,FALSE),"Not Carried")

 

[Supe]

I am in 2007. I copied and pasted both of the examples below and get a
message saying "The formula you typed contains and error" on both.

The answer from Per is just fine but if you are using Excel 2007 you can use
a simpler formula

=IFERROR(IF(AD3="x",VLOOKUP(A3,'[Access
Data.xlsx]Data_Qry'$A$2:$B$555555,2,FALSE),"Not Carried"), "No shipments")

or (easier to follow)
=IF(AD3<>"x","Not carried", IFERROR(VLOOKUP(A3,'[Access
Data.xlsx]Data_Qry'$A$2:$B$555555,2,FALSE), "No shipments")

Note you need only enter your formula once.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

I have a IF function that if a box is marked with an "x" then do a VLOOKUP
against a separate worksheet to get a shipment total. If there is no "x"
then that item isn't carried so it lists "Not Carried" in the cell. Issue
is
if there is an "x", but there isn't a match in the sheet I am doing the
VLOOKUP in the results come back as #N/A. Would like that result to say
"No
Shipments" instead of #N/A. This is beyond my Excel capabalities. The
formula I have so far is below.


=IF(AD3="x",VLOOKUP(A3,'[Access
Data.xlsx]Data_Qry'!$A$2:$B$555555,2,FALSE),"Not Carried")

 

[Supe]

Disregards previuos message. I put an ! in front of the VLOOKUP range and
then a box popped up with a suggested formula and that worked. Thanks.

The answer from Per is just fine but if you are using Excel 2007 you can use
a simpler formula

=IFERROR(IF(AD3="x",VLOOKUP(A3,'[Access
Data.xlsx]Data_Qry'$A$2:$B$555555,2,FALSE),"Not Carried"), "No shipments")

or (easier to follow)
=IF(AD3<>"x","Not carried", IFERROR(VLOOKUP(A3,'[Access
Data.xlsx]Data_Qry'$A$2:$B$555555,2,FALSE), "No shipments")

Note you need only enter your formula once.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

I have a IF function that if a box is marked with an "x" then do a VLOOKUP
against a separate worksheet to get a shipment total. If there is no "x"
then that item isn't carried so it lists "Not Carried" in the cell. Issue
is
if there is an "x", but there isn't a match in the sheet I am doing the
VLOOKUP in the results come back as #N/A. Would like that result to say
"No
Shipments" instead of #N/A. This is beyond my Excel capabalities. The
formula I have so far is below.


=IF(AD3="x",VLOOKUP(A3,'[Access
Data.xlsx]Data_Qry'!$A$2:$B$555555,2,FALSE),"Not Carried")