+ Reply to Thread
Results 1 to 3 of 3

Inconsistent results with =LOOKUP?

  1. #1
    watkincm
    Guest

    Inconsistent results with =LOOKUP?

    I am using Lookup across 2 worksheets.
    Where lookup finds a match, the result vector seems to work fine (too many
    rows to check them all). However, when no match is found, the result vector
    either returns a '1' or (in one instance only) a #N/A error.
    I would have expected the error rather than the '1' or am I missing something?

    The formula in sheet "2005": =LOOKUP($B3,'2004'!B$3:B$9689,'2004'!P$3:P$9689)
    Any ideas?
    --
    Mike Watkins

    --
    Mike Watkins

  2. #2
    SiC
    Guest

    RE: Inconsistent results with =LOOKUP?

    Hi Mike,

    The LOOKUP function does not usually return #N/A when there's no exact
    match. Rather, it goes to the next smallest value in the lookup vector and
    returns the corresponding result vector. The only time it returns #N/A is
    when your lookup value is smaller than the smallest value in the lookup
    vector. You might want to try using VLOOKUP instead, if you want to always
    return #N/A when there's no exact match. Hope this helps.

    -Simon

    "watkincm" wrote:

    > I am using Lookup across 2 worksheets.
    > Where lookup finds a match, the result vector seems to work fine (too many
    > rows to check them all). However, when no match is found, the result vector
    > either returns a '1' or (in one instance only) a #N/A error.
    > I would have expected the error rather than the '1' or am I missing something?
    >
    > The formula in sheet "2005": =LOOKUP($B3,'2004'!B$3:B$9689,'2004'!P$3:P$9689)
    > Any ideas?
    > --
    > Mike Watkins
    >
    > --
    > Mike Watkins


  3. #3
    watkincm
    Guest

    RE: Inconsistent results with =LOOKUP?

    Then LOOKUP was working correctly - that's exactly what it did...
    VLOOKUP works perfectly for what I need - thanks for your help

    --
    Mike Watkins


    "SiC" wrote:

    > Hi Mike,
    >
    > The LOOKUP function does not usually return #N/A when there's no exact
    > match. Rather, it goes to the next smallest value in the lookup vector and
    > returns the corresponding result vector. The only time it returns #N/A is
    > when your lookup value is smaller than the smallest value in the lookup
    > vector. You might want to try using VLOOKUP instead, if you want to always
    > return #N/A when there's no exact match. Hope this helps.
    >
    > -Simon
    >
    > "watkincm" wrote:
    >
    > > I am using Lookup across 2 worksheets.
    > > Where lookup finds a match, the result vector seems to work fine (too many
    > > rows to check them all). However, when no match is found, the result vector
    > > either returns a '1' or (in one instance only) a #N/A error.
    > > I would have expected the error rather than the '1' or am I missing something?
    > >
    > > The formula in sheet "2005": =LOOKUP($B3,'2004'!B$3:B$9689,'2004'!P$3:P$9689)
    > > Any ideas?
    > > --
    > > Mike Watkins
    > >
    > > --
    > > Mike Watkins


+ 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