+ Reply to Thread
Results 1 to 10 of 10

Formula to check expirations

  1. #1
    Registered User
    Join Date
    07-06-2009
    Location
    Ohio
    MS-Off Ver
    Excel 2007
    Posts
    8

    Formula to check expirations

    I have a direct mail company and lock in certain areas for a certain amount of time for my clients.

    I want to be able to check a hand full of zip codes to see if they are aviable for new clients.

    The lock in period is 60 days.

    For a zipcode to be available it can not be in my list or has passed the 60 day expiration.

    Attached file.

    Thanks!

  2. #2
    Registered User
    Join Date
    07-06-2009
    Location
    Ohio
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Formula to check expirations

    Forgot to add file. Here it is.

    Thanks for the help
    Attached Files Attached Files

  3. #3
    Valued Forum Contributor squiggler47's Avatar
    Join Date
    02-17-2009
    Location
    Littleborough, UK
    MS-Off Ver
    Excel 3.0 to 2007+2010! (couldnt get 2.1 working)
    Posts
    1,013

    Re: Formula to check expirations

    =INDEX(A2:G46,MATCH(I4,B2:B46),7)

    will find the zip code and return if it is available

    =MAX(INDEX(A2:G46,MATCH(I4,B2:B46),5),INDEX(A2:G46,MATCH(I4,B2:B46),6))

    will find the available date, which will either be today or the expiry day!
    Regards
    Darren

    Update 12-Nov-2010 Still job hunting!

    If you are happy with the results, please add to our reputation by clicking the blue scales icon in the blue bar of the post.

    Learn something new each day, Embrace change do not fear it, evolve and do not become extinct!


  4. #4
    Registered User
    Join Date
    07-06-2009
    Location
    Ohio
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Formula to check expirations

    Thank you very much! I wish I could contribute here, but I know zero about Excel.

  5. #5
    Registered User
    Join Date
    07-06-2009
    Location
    Ohio
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Formula to check expirations

    Still having a little bit of trouble with this. When I enter a zipcode that is not in the list, it returns a NO and the expiration date of the zip code nearest to the value that I enter. I've tried using different cell formats, but the results are the same. It appears at first glance to function properly, but if the zip code is not listed it will not function properly.

    Any help would be appreciated.

  6. #6
    Registered User
    Join Date
    07-06-2009
    Location
    Ohio
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Formula to check expirations

    Someone help my brain hurts!


    Thanks in advance.

  7. #7
    Registered User
    Join Date
    07-06-2009
    Location
    Ohio
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Formula to check expirations

    Any suggestions?

    Thanks!!

  8. #8
    Registered User
    Join Date
    07-06-2009
    Location
    Ohio
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Formula to check expirations

    Is this the impossible formula?

  9. #9
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Formula to check expirations

    i think ist is in j4
    =iferror(INDEX($A$2:$G$46,MATCH(I4,$B$2:$B$46,0),7),"Not found")
    and since you only need to find the first instance of yes (you have already done the 60day calc in col g anyway)
    in K4
    =iferror(INDEX($B$2:$B$46,MATCH("yes",$G$2:$G$46,0),0),"none free")
    Last edited by martindwilson; 08-21-2009 at 11:50 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  10. #10
    Registered User
    Join Date
    07-06-2009
    Location
    Ohio
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Formula to check expirations

    Thank you very much! I will give this a try.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1