Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Posts
    121
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Lookup Table (Excel 97)

    I have an excel spreadsheet with a lookup table. Using the formula =LOOKUP(A2, Pricing !B:B, PRICING!C:C), when I refer to an item not in the lookup table, it seems to generate it own value. Is there a way to return 0.

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Excel Lookup Table (Excel 97)

    According to Help "If LOOKUP can't find the lookup_value, it matches the largest value in lookup_vector that is less than or equal to lookup_value. The values in lookup_vector must be placed in ascending order .. otherwise, LOOKUP may not give the correct value". Could these issues be the problem?

    You may instead want to use VLOOKUP or HLOOKUP with the optional last argument as FALSE, that is

    =VLOOKUP(VALUE,TABLE,OFFSET,FALSE)

    See the Help to understand why, but also note that the most frequent use for setting this final parameter as FALSE is when TABLE is unsorted.

    Or you may want to use MATCH and OFFSET.

    If this doesn't help, perhaps you could post the workbook?
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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