Lookup a Canada postal code in a range of values
 

I have two spreadsheets: One with a list of various Canada postal codes
(e.g., V2N4Z9) and one with various ranges of Canada postal codes (e.g.,
Start Postal Code = V2R4Y4, End Postal Code = V2R5B7). However the postal
code ranges aren't consecutive. What Excel function/formula can I use to
determine if the postal code in the first spreadsheet is within one of the
postal code ranges in the second spreadsheet? Thanks.

Lookup a Canada postal code in a range of values
 

You would need numerical codes to do that, so in this case you would need
all codes


--


Regards,


Peo Sjoblom


"excellsi" wrote in message
...
I have two spreadsheets: One with a list of various Canada postal codes
(e.g., V2N4Z9) and one with various ranges of Canada postal codes (e.g.,
Start Postal Code = V2R4Y4, End Postal Code = V2R5B7). However the postal
code ranges aren't consecutive. What Excel function/formula can I use to
determine if the postal code in the first spreadsheet is within one of the
postal code ranges in the second spreadsheet? Thanks.

Lookup a Canada postal code in a range of values
 

The following formula assumes the target postal code is in C2, the start
codes are in column D, and the end codes are in column E:

=if(SUMPRODUCT(--($C$2D1:D5),--($C$2<E1:E5))0,"PC in one of the
ranges","PC not in any range")

Adjust your ranges to suit.

Regards,
Fred.


"excellsi" wrote in message
...
I have two spreadsheets: One with a list of various Canada postal codes
(e.g., V2N4Z9) and one with various ranges of Canada postal codes (e.g.,
Start Postal Code = V2R4Y4, End Postal Code = V2R5B7). However the postal
code ranges aren't consecutive. What Excel function/formula can I use to
determine if the postal code in the first spreadsheet is within one of the
postal code ranges in the second spreadsheet? Thanks.