| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
Match result is sometimes #N/A
Hi, I'm using the following formula. The problem is that sometimes
my value isn't found and I'm not getting a blank but rather #N/A. =IF(MATCH("POS*",B$83:B$782,0),"Please be advised the POS LOBs have been converted to PPO LOBs", "") 1. Is there a fix to my formula? or 2. In the cell that results #N/A - I could set a conditional format to make a white background and white font. However, when I try that - it's not working either. Cell format = #N/A doesn't do what I want. |
|
#2
|
|||
|
|||
|
Try this, Sharon:
=IF(ISNA(MATCH("POS*",B$83:B$782,0),"Please be advised the POS LOBs have been converted to PPO LOBs", ""),"",MATCH("POS*",B$83:B$782,0),"Please be advised the POS LOBs have been converted to PPO LOBs", "")) ************ Anne Troy www.OfficeArticles.com "SharonP." wrote in message ... Hi, I'm using the following formula. The problem is that sometimes my value isn't found and I'm not getting a blank but rather #N/A. =IF(MATCH("POS*",B$83:B$782,0),"Please be advised the POS LOBs have been converted to PPO LOBs", "") 1. Is there a fix to my formula? or 2. In the cell that results #N/A - I could set a conditional format to make a white background and white font. However, when I try that - it's not working either. Cell format = #N/A doesn't do what I want. |
|
#3
|
|||
|
|||
|
=IF(ISNA(Your_Formula),"",Your_Formula)
-- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ------------------------------*------------------------------*---------------- It's easier to beg forgiveness than ask permission :-) ------------------------------*------------------------------*---------------- "SharonP." wrote in message ... Hi, I'm using the following formula. The problem is that sometimes my value isn't found and I'm not getting a blank but rather #N/A. =IF(MATCH("POS*",B$83:B$782,0),"Please be advised the POS LOBs have been converted to PPO LOBs", "") 1. Is there a fix to my formula? or 2. In the cell that results #N/A - I could set a conditional format to make a white background and white font. However, when I try that - it's not working either. Cell format = #N/A doesn't do what I want. |
|
#4
|
|||
|
|||
|
Sorry...
=If(ISNA(MATCH("POS*",B$83:B$782,0)),"",MATCH("POS *",B$83:B$782,0),"Please be advised the POS LOBs have been converted to PPO LOBs", "") ************ Anne Troy www.OfficeArticles.com "SharonP." wrote in message ... Hi, I'm using the following formula. The problem is that sometimes my value isn't found and I'm not getting a blank but rather #N/A. =IF(MATCH("POS*",B$83:B$782,0),"Please be advised the POS LOBs have been converted to PPO LOBs", "") 1. Is there a fix to my formula? or 2. In the cell that results #N/A - I could set a conditional format to make a white background and white font. However, when I try that - it's not working either. Cell format = #N/A doesn't do what I want. |
|
#5
|
|||
|
|||
|
Thanks for getting me started - getting an error with this formula - probably
something silly - late in the day. =IF(ISNA(MATCH ("POS*",B$83:B$782,0)),"", MATCH "POS*",B$83:B$782,0),"Please be advised the POS LOBs have been converted to PPO LOBs", "") "Anne Troy" wrote: Sorry... =If(ISNA(MATCH("POS*",B$83:B$782,0)),"",MATCH("POS *",B$83:B$782,0),"Please be advised the POS LOBs have been converted to PPO LOBs", "") ************ Anne Troy www.OfficeArticles.com "SharonP." wrote in message ... Hi, I'm using the following formula. The problem is that sometimes my value isn't found and I'm not getting a blank but rather #N/A. =IF(MATCH("POS*",B$83:B$782,0),"Please be advised the POS LOBs have been converted to PPO LOBs", "") 1. Is there a fix to my formula? or 2. In the cell that results #N/A - I could set a conditional format to make a white background and white font. However, when I try that - it's not working either. Cell format = #N/A doesn't do what I want. |
|
#6
|
|||
|
|||
|
=IF(ISNA(MATCH ("POS*",B$83:B$782,0)),"",MATCH ("POS*",B$83:B$782,0),"Please
be advised the POS LOBs have been converted to PPO LOBs") Try that, I think. ************ Anne Troy www.OfficeArticles.com "SharonP." wrote in message ... Thanks for getting me started - getting an error with this formula - probably something silly - late in the day. =IF(ISNA(MATCH ("POS*",B$83:B$782,0)),"", MATCH "POS*",B$83:B$782,0),"Please be advised the POS LOBs have been converted to PPO LOBs", "") "Anne Troy" wrote: Sorry... =If(ISNA(MATCH("POS*",B$83:B$782,0)),"",MATCH("POS *",B$83:B$782,0),"Please be advised the POS LOBs have been converted to PPO LOBs", "") ************ Anne Troy www.OfficeArticles.com "SharonP." wrote in message ... Hi, I'm using the following formula. The problem is that sometimes my value isn't found and I'm not getting a blank but rather #N/A. =IF(MATCH("POS*",B$83:B$782,0),"Please be advised the POS LOBs have been converted to PPO LOBs", "") 1. Is there a fix to my formula? or 2. In the cell that results #N/A - I could set a conditional format to make a white background and white font. However, when I try that - it's not working either. Cell format = #N/A doesn't do what I want. |
|
#7
|
|||
|
|||
|
My snytax is still incorrect - I'm getting the error -
"You've entered too many arguments for this function". The following is a cut and paste of my formula - =IF(ISNA(MATCH("POS",B$83:B$782,0))," ",MATCH("POS",B$83:B$782,0), "Please be advised the POS LOBs have been converted to PPO LOBs") This formula looks right to me. Excel frustrates me so bad some days! "Anne Troy" wrote: =IF(ISNA(MATCH ("POS*",B$83:B$782,0)),"",MATCH ("POS*",B$83:B$782,0),"Please be advised the POS LOBs have been converted to PPO LOBs") Try that, I think. ************ Anne Troy www.OfficeArticles.com "SharonP." wrote in message ... Thanks for getting me started - getting an error with this formula - probably something silly - late in the day. =IF(ISNA(MATCH ("POS*",B$83:B$782,0)),"", MATCH "POS*",B$83:B$782,0),"Please be advised the POS LOBs have been converted to PPO LOBs", "") "Anne Troy" wrote: Sorry... =If(ISNA(MATCH("POS*",B$83:B$782,0)),"",MATCH("POS *",B$83:B$782,0),"Please be advised the POS LOBs have been converted to PPO LOBs", "") ************ Anne Troy www.OfficeArticles.com "SharonP." wrote in message ... Hi, I'm using the following formula. The problem is that sometimes my value isn't found and I'm not getting a blank but rather #N/A. =IF(MATCH("POS*",B$83:B$782,0),"Please be advised the POS LOBs have been converted to PPO LOBs", "") 1. Is there a fix to my formula? or 2. In the cell that results #N/A - I could set a conditional format to make a white background and white font. However, when I try that - it's not working either. Cell format = #N/A doesn't do what I want. |
|
#8
|
|||
|
|||
|
You don't need the second MATCH formula:
=IF(ISNA(MATCH("POS*",B$83:B$782,0))," ","Please be advised the POS LOBs have been converted to PPO LOBs") SharonP. wrote: My snytax is still incorrect - I'm getting the error - "You've entered too many arguments for this function". The following is a cut and paste of my formula - =IF(ISNA(MATCH("POS",B$83:B$782,0))," ",MATCH("POS",B$83:B$782,0), "Please be advised the POS LOBs have been converted to PPO LOBs") This formula looks right to me. Excel frustrates me so bad some days! "Anne Troy" wrote: =IF(ISNA(MATCH ("POS*",B$83:B$782,0)),"",MATCH ("POS*",B$83:B$782,0),"Please be advised the POS LOBs have been converted to PPO LOBs") Try that, I think. ************ Anne Troy www.OfficeArticles.com "SharonP." wrote in message ... Thanks for getting me started - getting an error with this formula - probably something silly - late in the day. =IF(ISNA(MATCH ("POS*",B$83:B$782,0)),"", MATCH "POS*",B$83:B$782,0),"Please be advised the POS LOBs have been converted to PPO LOBs", "") "Anne Troy" wrote: Sorry... =If(ISNA(MATCH("POS*",B$83:B$782,0)),"",MATCH( "POS*",B$83:B$782,0),"Please be advised the POS LOBs have been converted to PPO LOBs", "") ************ Anne Troy www.OfficeArticles.com "SharonP." wrote in message ... Hi, I'm using the following formula. The problem is that sometimes my value isn't found and I'm not getting a blank but rather #N/A. =IF(MATCH("POS*",B$83:B$782,0),"Please be advised the POS LOBs have been converted to PPO LOBs", "") 1. Is there a fix to my formula? or 2. In the cell that results #N/A - I could set a conditional format to make a white background and white font. However, when I try that - it's not working either. Cell format = #N/A doesn't do what I want. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
|
#9
|
|||
|
|||
|
Try...
=IF(ISNUMBER(MATCH("POS",B$83:B$782,0)),MATCH("POS ",B$83:B$782,0), "Please be advised the POS LOBs have been converted to PPO LOBs") Hope this helps! In article , "SharonP." wrote: My snytax is still incorrect - I'm getting the error - "You've entered too many arguments for this function". The following is a cut and paste of my formula - =IF(ISNA(MATCH("POS",B$83:B$782,0))," ",MATCH("POS",B$83:B$782,0), "Please be advised the POS LOBs have been converted to PPO LOBs") This formula looks right to me. Excel frustrates me so bad some days! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I NEED HELP with the SPELLNUMBER Function | Excel Worksheet Functions | |||
| EXCEL:NUMBER TO GREEK WORDS | Excel Worksheet Functions | |||
| Conversion | Excel Worksheet Functions | |||
| Is there a formula to spell out a number in excel? | Excel Worksheet Functions | |||
| Spellnumber | Excel Worksheet Functions | |||
Hybrid Mode
