IF/VLOOKUP with #N/A results

S

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")
 
P

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
 
L

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")
 
B

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
 
S

Supe

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


Per Jessen said:
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

Supe said:
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")
Click to expand...
Click to expand...
 
S

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.


Bernard Liengme said:
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


Supe said:
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")
Click to expand...
Click to expand...
 
S

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.

Bernard Liengme said:
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


Supe said:
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")
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Remove #n/a in vlookup to sum results 6
Multi Vlookup #N/A Error ! 4
If condition not met get #N/A 5
Hiding VLookup #N/A 1
VLOOKUP returning #N/A result 2
Vlookup - N/A 4
Vlookup if #N/A then enter enter data in cell I4 3
Alternative for Vlookup output of #N/A if data not found? 4

Top