Results 1 to 2 of 2
Thread: Excel Lookup Table (Excel 97)
2002-06-20, 18:38 #1
- Join Date
- May 2002
- 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.
2002-06-20, 18:49 #2
- Join Date
- Dec 2000
- Salt Lake City, Utah, USA
- 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
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