Results 1 to 4 of 4

20030311, 08:46 #1
 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 (nonzero) 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

20030311, 08:53 #2
 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, ..., AZ, 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, ..., AZ, 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, ZA,...2, 1, 0, 1, 2,..., and so on.Jan Karel Pieterse
Microsoft Excel MVP, WMVP
www.jkpads.com
Professional Office Developers Association

20030311, 09:17 #3
 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

20030311, 10:11 #4
 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