Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Nov 2001
    Location
    Wellington, New Zealand
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lookup - exact match (2002sp2 / any)

    Lookup (array & vector) (also Match, etc) only match to the next lower value in the lookup_vector; ie. "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".

    I find this frustrating. Most times I use "lookup", I only want a (non-zero) value returned if there is an exact match, otherwise I (usually) want "0", or occasionally some other (specifiable) result such as "Error".

    I have never understood why this functionality is not (optionally) available (ASAIK) - eg. by a switch or variation of name. In the example below, I'll call it "XLOOKUP"

    eg: for

    ZIP POP
    90001 10000
    91640 34679
    93450 21356

    I want XLOOKUP("91640", ZIP, POP) to return "34679"
    but XLOOKUP("91140", ZIP, POP) to return "0"

    since in this example ZIP "91140" has no POP defined, which can usually be equated to a POP of "0".
    I definitely do not want XLOOKUP("91140", ZIP, POP) to return "10000", which is plain wrong.

    IS there any simple equivalent function that would do this task?
    I would have thought this type of application very common.

    I do have a workaround using array formulae as follows:

    {=SUM(IF(91140=ZIP, POP,0))} (dropping the text qualifiers introduced for clarity in the XLOOKUP example)

    This works fine, but I often feel uncomfortable about the "SUM" that is required to get the array formula to function (eg. in case I accidentally double up on a ZIP value - in that case I would want the function to report an error, or take some specifiable action). Unfortunately {=IF(91140=ZIP, POP,0)} does not work, even if all ZIP values are unique.

    Any (SIMPLE) alternative suggestions appreciated.

    David

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup - exact match (2002sp2 / any)

    The VLOOKUP and HLOOKUP functions have an additional argument that controls this behaviour.

    From XL2000 help:

    VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

    Lookup_value is the value to be found in the first column of the array. Lookup_value can be a value, a reference, or a text string.

    Table_array is the table of information in which data is looked up. Use a reference to a range or a range name, such as Database or List.

    If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise VLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted.


    You can put the values in ascending order by choosing the Sort command from the Data menu and selecting Ascending.


    The values in the first column of table_array can be text, numbers, or logical values.


    Uppercase and lowercase text are equivalent.
    Col_index_num is the column number in table_array from which the matching value must be returned. A col_index_num of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on. If col_index_num is less than 1, VLOOKUP returns the #VALUE! error value; if col_index_num is greater than the number of columns in table_array, VLOOKUP returns the #REF! error value.

    Range_lookup is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will find an exact match. If one is not found, the error value #N/A is returned.

    Also, MATCH has an argument that controls what is returned when no exact mact is found:

    MATCH(lookup_value,lookup_array,match_type)

    Lookup_value is the value you use to find the value you want in a table.

    Lookup_value is the value you want to match in lookup_array. For example, when you look up someone's number in a telephone book, you are using the person's name as the lookup value, but the telephone number is the value you want.


    Lookup_value can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.

    Lookup_array is a contiguous range of cells containing possible lookup values. Lookup_array can be an array or an array reference.

    Match_type is the number -1, 0, or 1. Match_type specifies how Microsoft Excel matches lookup_value with values in lookup_array.

    If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.

    If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. Lookup_array can be in any order.

    If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order: TRUE, FALSE, Z-A,...2, 1, 0, -1, -2,..., and so on.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Star Lounger
    Join Date
    Nov 2001
    Location
    Wellington, New Zealand
    Posts
    79
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookup - exact match (2002sp2 / any)

    Many thanks for such a prompt response. I had not appreciated that.

    In my current implementation, I would like it to return a value of zero if no exact match (ZIP's not in the lookup_vector being deemed to have a "population" of zero). Hence, for this case I will continue with my array formula (having made sure that all ZIP's are unique).

    David

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Lookup - exact match (2002sp2 / any)

    You can use something like:
    if (isna(VLOOKUP("91640", ZIP, POP,false)),0,VLOOKUP("91640", ZIP, POP,false))

    Steve

Posting Permissions

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