Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to return "n/a" when no match is found.
I've got a workbook w/ one sheet that has downloaded fuel tax rates named
CurrentTaxRates. On another sheet in the workbook, I'm trying to obtain the tax rates via lookup functions for particular states. Some states, but not all, have a surcharge rate in addition to the tax rate. I have the following formula which is giving me the surcharge rate for the states that have one. However, if a state does not have a surcharge rate, it's giving me an error - #VALUE! How do write it so that if a state does not have a surcharge rate, it returns "N/A" ? Thanks for your help =IF(VLOOKUP(CONCATENATE($A61 & " schg"),CurrentTaxRates,2,FALSE),NA,VLOOKUP(CONCATE NATE($A61 & " schg"),CurrentTaxRates,2,FALSE)) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to return "n/a" when no match is found.
This will return #N/A all by itself if the lookup value is not found.
=VLOOKUP(CONCATENATE($A61 & " schg"),CurrentTaxRates,2,FALSE) "Loni - RWT" wrote in message ... I've got a workbook w/ one sheet that has downloaded fuel tax rates named CurrentTaxRates. On another sheet in the workbook, I'm trying to obtain the tax rates via lookup functions for particular states. Some states, but not all, have a surcharge rate in addition to the tax rate. I have the following formula which is giving me the surcharge rate for the states that have one. However, if a state does not have a surcharge rate, it's giving me an error - #VALUE! How do write it so that if a state does not have a surcharge rate, it returns "N/A" ? Thanks for your help =IF(VLOOKUP(CONCATENATE($A61 & " schg"),CurrentTaxRates,2,FALSE),NA,VLOOKUP(CONCATE NATE($A61 & " schg"),CurrentTaxRates,2,FALSE)) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to return "n/a" when no match is found.
If #N/A is okay, juust use
=VLOOKUP(CONCATENATE($A61 & " schg"),CurrentTaxRates,2,FALSE) else use =IF(ISNA(VLOOKUP(CONCATENATE($A61 & " schg"),CurrentTaxRates,2,FALSE)),"N/A", VLOOKUP(CONCATENATE($A61 & " schg"),CurrentTaxRates,2,FALSE)) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Loni - RWT" wrote in message ... I've got a workbook w/ one sheet that has downloaded fuel tax rates named CurrentTaxRates. On another sheet in the workbook, I'm trying to obtain the tax rates via lookup functions for particular states. Some states, but not all, have a surcharge rate in addition to the tax rate. I have the following formula which is giving me the surcharge rate for the states that have one. However, if a state does not have a surcharge rate, it's giving me an error - #VALUE! How do write it so that if a state does not have a surcharge rate, it returns "N/A" ? Thanks for your help =IF(VLOOKUP(CONCATENATE($A61 & " schg"),CurrentTaxRates,2,FALSE),NA,VLOOKUP(CONCATE NATE($A61 & " schg"),CurrentTaxRates,2,FALSE)) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to return "n/a" when no match is found.
ps.
You can use: =IF(VLOOKUP(CONCATENATE($A61,"schg"), ... or =IF(VLOOKUP($A61&"schg"), ... But you don't need to concatenate a string that's already concatenated. Loni - RWT wrote: I've got a workbook w/ one sheet that has downloaded fuel tax rates named CurrentTaxRates. On another sheet in the workbook, I'm trying to obtain the tax rates via lookup functions for particular states. Some states, but not all, have a surcharge rate in addition to the tax rate. I have the following formula which is giving me the surcharge rate for the states that have one. However, if a state does not have a surcharge rate, it's giving me an error - #VALUE! How do write it so that if a state does not have a surcharge rate, it returns "N/A" ? Thanks for your help =IF(VLOOKUP(CONCATENATE($A61 & " schg"),CurrentTaxRates,2,FALSE),NA,VLOOKUP(CONCATE NATE($A61 & " schg"),CurrentTaxRates,2,FALSE)) -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to return "n/a" when no match is found.
Thanks for your reply. That is what I started with but got #Value! when
there was no match for the lookup value which is why I was trying to incorporate the IF statement. In another post concerning a cell calculation, I unchecked the "transition formula evaluation" option and when I did, the VLookup function returned #N/A on no match. "Tyro" wrote: This will return #N/A all by itself if the lookup value is not found. =VLOOKUP(CONCATENATE($A61 & " schg"),CurrentTaxRates,2,FALSE) "Loni - RWT" wrote in message ... I've got a workbook w/ one sheet that has downloaded fuel tax rates named CurrentTaxRates. On another sheet in the workbook, I'm trying to obtain the tax rates via lookup functions for particular states. Some states, but not all, have a surcharge rate in addition to the tax rate. I have the following formula which is giving me the surcharge rate for the states that have one. However, if a state does not have a surcharge rate, it's giving me an error - #VALUE! How do write it so that if a state does not have a surcharge rate, it returns "N/A" ? Thanks for your help =IF(VLOOKUP(CONCATENATE($A61 & " schg"),CurrentTaxRates,2,FALSE),NA,VLOOKUP(CONCATE NATE($A61 & " schg"),CurrentTaxRates,2,FALSE)) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to return "n/a" when no match is found.
This formula works fine for me in Excel 2007. It returns a value from the
table if the lookup value exists, if not it returns #N/A =VLOOKUP($A61 & " schg",CurrentTaxRates,2,FALSE) I seem to remember that "transistion formula evaluation" has something to do with Lotus 123. Are you working with or converting 123 workbooks? Tyro "Loni - RWT" wrote in message ... Thanks for your reply. That is what I started with but got #Value! when there was no match for the lookup value which is why I was trying to incorporate the IF statement. In another post concerning a cell calculation, I unchecked the "transition formula evaluation" option and when I did, the VLookup function returned #N/A on no match. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
function to return day in the form "Monday", "Tuesday" etc given . | Excel Worksheet Functions | |||
Check if cells contain the word "Thailand", return "TRUE" | Excel Worksheet Functions | |||
Temporary Array i.e. match "this" and return entire row | Excel Discussion (Misc queries) | |||
If (A1 = "ValueA" & C1 = "ValueB") return the quantity of matches | Excel Discussion (Misc queries) | |||
"No RETURN() or HALT() function found on macro sheet." | Excel Worksheet Functions |