Match result is sometimes #N/A

[Guest]

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.

 

[Anne Troy]

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

 

[Ken Wright]

=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
------------------------------­------------------------------­----------------

 

[Anne Troy]

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

 

[Guest]

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]

=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

 

[Guest]

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!

 

[Debra Dalgleish]

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

 

[Domenic]

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!

 

[Guest]

Success finally! Thanks everyone for the guidance in putting this formula
together.