+ Reply to Thread
Results 1 to 6 of 6

Unwanted result in LOOKUP()

  1. #1
    Sympatico News
    Guest

    Unwanted result in LOOKUP()

    I want to avoid getting "N/A" as a result when there is no match on the
    formula below. What I need is a blank on no match but an "S" when it does.

    How do we do this? Thought this would work.....

    =IF(LOOKUP(A3,!$H14:$H27)=A3,"S","")



  2. #2
    Anne Troy
    Guest

    Re: Unwanted result in LOOKUP()

    =if(isna(lookup(A3,!$H14:$H27),"","S")

    If it's always S or blank, you don't need to match A3...
    *******************
    ~Anne Troy

    www.OfficeArticles.com


    "Sympatico News" <[email protected]> wrote in message
    news:[email protected]...
    > I want to avoid getting "N/A" as a result when there is no match on the
    > formula below. What I need is a blank on no match but an "S" when it does.
    >
    > How do we do this? Thought this would work.....
    >
    > =IF(LOOKUP(A3,!$H14:$H27)=A3,"S","")
    >
    >




  3. #3
    KL
    Guest

    Re: Unwanted result in LOOKUP()

    Hi,

    Try this:

    =IF(ISNA(LOOKUP(A3,!$H14:$H27)),"","S")

    Regards,
    KL


    "Sympatico News" <[email protected]> wrote in message
    news:[email protected]...
    >I want to avoid getting "N/A" as a result when there is no match on the
    >formula below. What I need is a blank on no match but an "S" when it does.
    >
    > How do we do this? Thought this would work.....
    >
    > =IF(LOOKUP(A3,!$H14:$H27)=A3,"S","")
    >




  4. #4
    Sympatico News
    Guest

    Re: Unwanted result in LOOKUP()

    very nice - thankyou. I'm on track but there's a problem here though. I want
    to copy this across the row. The first time I hit a match "S" is returned as
    expected. Problem is that all cells after also return an "S" match or not.

    The sheet is looking for Stat holidays in the lookup H14:H27. Blank is
    returned for 24, 25 which is correct of course, "S" is returned for the 26
    and 27 which is also correct but the 28 to 01 should be a blank. 02-Jan is a
    valid "S" of course.

    My previous formula did a similar thing except I got #N/A until a match
    occurred - then the function did what is was supposed to.


    S S S S S S S S
    24-Dec-05 25-Dec-05 26-Dec-05 27-Dec-05 28-Dec-05 29-Dec-05 30-Dec-05
    31-Dec-05 1-Jan-06 2-Jan-06
    S S M T W T F S S M



    =if(isna(lookup(A3,$H14:$H27),"","S")


    Gerry Walsh


    "Anne Troy" <[email protected]> wrote in message
    news:[email protected]...
    > =if(isna(lookup(A3,!$H14:$H27),"","S")
    >
    > If it's always S or blank, you don't need to match A3...
    > *******************
    > ~Anne Troy
    >
    > www.OfficeArticles.com
    >
    >
    > "Sympatico News" <[email protected]> wrote in message
    > news:[email protected]...
    >> I want to avoid getting "N/A" as a result when there is no match on the
    >> formula below. What I need is a blank on no match but an "S" when it
    >> does.
    >>
    >> How do we do this? Thought this would work.....
    >>
    >> =IF(LOOKUP(A3,!$H14:$H27)=A3,"S","")
    >>
    >>

    >
    >




  5. #5
    KL
    Guest

    Re: Unwanted result in LOOKUP()

    Hi,

    You should probably make the row references absolute too:

    =IF(ISNA(LOOKUP(A3,!$H$14:$H$27)),"","S")

    Regards,
    KL


    "Sympatico News" <[email protected]> wrote in message
    news:[email protected]...
    > very nice - thankyou. I'm on track but there's a problem here though. I
    > want to copy this across the row. The first time I hit a match "S" is
    > returned as expected. Problem is that all cells after also return an "S"
    > match or not.
    >
    > The sheet is looking for Stat holidays in the lookup H14:H27. Blank is
    > returned for 24, 25 which is correct of course, "S" is returned for the 26
    > and 27 which is also correct but the 28 to 01 should be a blank. 02-Jan is
    > a valid "S" of course.
    >
    > My previous formula did a similar thing except I got #N/A until a match
    > occurred - then the function did what is was supposed to.
    >
    >
    > S S S S S S S S
    > 24-Dec-05 25-Dec-05 26-Dec-05 27-Dec-05 28-Dec-05 29-Dec-05 30-Dec-05
    > 31-Dec-05 1-Jan-06 2-Jan-06
    > S S M T W T F S S M
    >
    >
    >
    > =if(isna(lookup(A3,$H14:$H27),"","S")
    >
    >
    > Gerry Walsh
    >
    >
    > "Anne Troy" <[email protected]> wrote in message
    > news:[email protected]...
    >> =if(isna(lookup(A3,!$H14:$H27),"","S")
    >>
    >> If it's always S or blank, you don't need to match A3...
    >> *******************
    >> ~Anne Troy
    >>
    >> www.OfficeArticles.com
    >>
    >>
    >> "Sympatico News" <[email protected]> wrote in message
    >> news:[email protected]...
    >>> I want to avoid getting "N/A" as a result when there is no match on the
    >>> formula below. What I need is a blank on no match but an "S" when it
    >>> does.
    >>>
    >>> How do we do this? Thought this would work.....
    >>>
    >>> =IF(LOOKUP(A3,!$H14:$H27)=A3,"S","")
    >>>
    >>>

    >>
    >>

    >
    >




  6. #6
    Sympatico News
    Guest

    Re: Unwanted result in LOOKUP()

    Solved - need an exact match that only VLOOKUP can give me.

    =IF(ISNA(VLOOKUP(C3,'Fiscal Years'!$B13:$B27,1,FALSE)),"","S")

    Thankyou both

    "KL" <[email protected]> wrote in message
    news:[email protected]...
    > Hi,
    >
    > You should probably make the row references absolute too:
    >
    > =IF(ISNA(LOOKUP(A3,!$H$14:$H$27)),"","S")
    >
    > Regards,
    > KL
    >
    >
    > "Sympatico News" <[email protected]> wrote in message
    > news:[email protected]...
    >> very nice - thankyou. I'm on track but there's a problem here though. I
    >> want to copy this across the row. The first time I hit a match "S" is
    >> returned as expected. Problem is that all cells after also return an "S"
    >> match or not.
    >>
    >> The sheet is looking for Stat holidays in the lookup H14:H27. Blank is
    >> returned for 24, 25 which is correct of course, "S" is returned for the
    >> 26 and 27 which is also correct but the 28 to 01 should be a blank.
    >> 02-Jan is a valid "S" of course.
    >>
    >> My previous formula did a similar thing except I got #N/A until a match
    >> occurred - then the function did what is was supposed to.
    >>
    >>
    >> S S S S S S S S
    >> 24-Dec-05 25-Dec-05 26-Dec-05 27-Dec-05 28-Dec-05 29-Dec-05
    >> 30-Dec-05 31-Dec-05 1-Jan-06 2-Jan-06
    >> S S M T W T F S S M
    >>
    >>
    >>
    >> =if(isna(lookup(A3,$H14:$H27),"","S")
    >>
    >>
    >> Gerry Walsh
    >>
    >>
    >> "Anne Troy" <[email protected]> wrote in message
    >> news:[email protected]...
    >>> =if(isna(lookup(A3,!$H14:$H27),"","S")
    >>>
    >>> If it's always S or blank, you don't need to match A3...
    >>> *******************
    >>> ~Anne Troy
    >>>
    >>> www.OfficeArticles.com
    >>>
    >>>
    >>> "Sympatico News" <[email protected]> wrote in message
    >>> news:[email protected]...
    >>>> I want to avoid getting "N/A" as a result when there is no match on the
    >>>> formula below. What I need is a blank on no match but an "S" when it
    >>>> does.
    >>>>
    >>>> How do we do this? Thought this would work.....
    >>>>
    >>>> =IF(LOOKUP(A3,!$H14:$H27)=A3,"S","")
    >>>>
    >>>>
    >>>
    >>>

    >>
    >>

    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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