| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
IF/VLOOKUP with #N/A results
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") |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF/VLOOKUP with #N/A results
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" skrev i meddelelsen ... 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") |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF/VLOOKUP with #N/A results
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") -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Supe" wrote: 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") |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF/VLOOKUP with #N/A results
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" wrote in message ... 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") |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF/VLOOKUP with #N/A results
I tried entering this formula and I get a message saying "You've entered too
many arguments for this function". "Per Jessen" wrote: 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" skrev i meddelelsen ... 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") |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF/VLOOKUP with #N/A results
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" wrote: 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" wrote in message ... 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") |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
IF/VLOOKUP with #N/A results
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" wrote: 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" wrote in message ... 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") |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sum results of VLOOKUP | Excel Worksheet Functions | |||
| vlookup results in 0 | Excel Discussion (Misc queries) | |||
| Vlookup with two results | Excel Discussion (Misc queries) | |||
| to sum up all value results from VLOOKUP | Excel Worksheet Functions | |||
| how do you add vlookup results? | Excel Worksheet Functions | |||
Linear Mode
