+ Reply to Thread
Results 1 to 9 of 9

Lookup:returns a blank

  1. #1
    tracy
    Guest

    Lookup:returns a blank

    I have a Lookup command and it's working for everything but the last entry in
    the source list
    =LOOKUP(A8,WKFLDS!A14:A39,WKFLDS!B14:B39)
    Entries in rows A14:A36 are found successfully, but A37 returns a blank even
    though there is data in B37
    Rows 38 and 39 are currently blank

  2. #2
    bpeltzer
    Guest

    re: Lookup:returns a blank

    Could you clarify...
    What cell is this formula in, and how are you changing it to look up
    different entries in the source list? If you're doing that by autofilling
    the formula below (or copying and pasting it into additional rows), I suspect
    the problem is that the relative references are causing the table reference
    to advance off the table. Enter the formula using absolute references for
    the table: =LOOKUP(A8,WKFLDS!$A$14:$A$39,WKFLDS!$B$14:$B$39)
    --Bruce

    "tracy" wrote:

    > I have a Lookup command and it's working for everything but the last entry in
    > the source list
    > =LOOKUP(A8,WKFLDS!A14:A39,WKFLDS!B14:B39)
    > Entries in rows A14:A36 are found successfully, but A37 returns a blank even
    > though there is data in B37
    > Rows 38 and 39 are currently blank


  3. #3
    Kevin Vaughn
    Guest

    re: Lookup:returns a blank

    You need to "anchor" your lookup range else as you copy the formula down, the
    lookup range moves (and you could experience what you are experiencing.)

    Use absolute references for those ranges (by preceeding the cell/column with
    $. For your formula, try:

    =LOOKUP(A8,WKFLDS!$A$14:$A$39,WKFLDS!$B$14:$B$39)

    --
    Kevin Vaughn


    "tracy" wrote:

    > I have a Lookup command and it's working for everything but the last entry in
    > the source list
    > =LOOKUP(A8,WKFLDS!A14:A39,WKFLDS!B14:B39)
    > Entries in rows A14:A36 are found successfully, but A37 returns a blank even
    > though there is data in B37
    > Rows 38 and 39 are currently blank


  4. #4
    tracy
    Guest

    re: Lookup:returns a blank

    My range is correct.
    I was testing out the formula for all values between A14 and A39 and the
    only one that doesn't work is A37
    I changed the formula to anchor the range and it still is a problem

    "tracy" wrote:

    > I have a Lookup command and it's working for everything but the last entry in
    > the source list
    > =LOOKUP(A8,WKFLDS!A14:A39,WKFLDS!B14:B39)
    > Entries in rows A14:A36 are found successfully, but A37 returns a blank even
    > though there is data in B37
    > Rows 38 and 39 are currently blank


  5. #5
    paul
    Guest

    re: Lookup:returns a blank

    Important The values in array must be placed in ascending order: ...,-2, -1,
    0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not give the correct
    value. Uppercase and lowercase text are equivalent
    --
    paul
    remove nospam for email addy!



    "tracy" wrote:

    > I have a Lookup command and it's working for everything but the last entry in
    > the source list
    > =LOOKUP(A8,WKFLDS!A14:A39,WKFLDS!B14:B39)
    > Entries in rows A14:A36 are found successfully, but A37 returns a blank even
    > though there is data in B37
    > Rows 38 and 39 are currently blank


  6. #6
    tracy
    Guest

    re: Lookup:returns a blank

    The values are in ascending order.
    project a
    project b
    ....
    project x is the last in the list and it's the one that doesn't return a
    value. It returns blanks not #N/A


    "paul" wrote:

    > Important The values in array must be placed in ascending order: ...,-2, -1,
    > 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not give the correct
    > value. Uppercase and lowercase text are equivalent
    > --
    > paul
    > remove nospam for email addy!
    >
    >
    >
    > "tracy" wrote:
    >
    > > I have a Lookup command and it's working for everything but the last entry in
    > > the source list
    > > =LOOKUP(A8,WKFLDS!A14:A39,WKFLDS!B14:B39)
    > > Entries in rows A14:A36 are found successfully, but A37 returns a blank even
    > > though there is data in B37
    > > Rows 38 and 39 are currently blank


  7. #7
    tracy
    Guest

    re: Lookup:returns a blank

    Never mind...I switched to VLOOKUP and got it to work....
    Thanks for your help.

    "tracy" wrote:

    > I have a Lookup command and it's working for everything but the last entry in
    > the source list
    > =LOOKUP(A8,WKFLDS!A14:A39,WKFLDS!B14:B39)
    > Entries in rows A14:A36 are found successfully, but A37 returns a blank even
    > though there is data in B37
    > Rows 38 and 39 are currently blank


  8. #8
    Kevin Vaughn
    Guest

    re: Lookup:returns a blank

    Based on everything you said, and based on some testing I did, I would ask if
    you copy/pasted the formula when posting your message or typed it in. If
    your formula were slightly different, I could see you getting the results you
    stated:

    =LOOKUP(A8,WKFLDS!A14:A39,WKFLDS!B15:B39)

    If that isn't it, I don't know what it is.
    --
    Kevin Vaughn


    "tracy" wrote:

    > The values are in ascending order.
    > project a
    > project b
    > ...
    > project x is the last in the list and it's the one that doesn't return a
    > value. It returns blanks not #N/A
    >
    >
    > "paul" wrote:
    >
    > > Important The values in array must be placed in ascending order: ...,-2, -1,
    > > 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not give the correct
    > > value. Uppercase and lowercase text are equivalent
    > > --
    > > paul
    > > remove nospam for email addy!
    > >
    > >
    > >
    > > "tracy" wrote:
    > >
    > > > I have a Lookup command and it's working for everything but the last entry in
    > > > the source list
    > > > =LOOKUP(A8,WKFLDS!A14:A39,WKFLDS!B14:B39)
    > > > Entries in rows A14:A36 are found successfully, but A37 returns a blank even
    > > > though there is data in B37
    > > > Rows 38 and 39 are currently blank


  9. #9
    Registered User
    Join Date
    11-13-2004
    Posts
    46

    lookup

    this is a test

+ 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