Lookup a Canada postal code in a range of values

E

excellsi

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.
 
P

Peo Sjoblom

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


--


Regards,


Peo Sjoblom
 
F

Fred Smith

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$2>D1:D5),--($C$2<E1:E5))>0,"PC in one of the
ranges","PC not in any range")

Adjust your ranges to suit.

Regards,
Fred.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Comparing a Postal Code against a Zone Table from UPS 7
international postal codes 3
Help with VLookup function 6
Lookup Function Not in Ascending Order 14
Return data from corresponding range 1
for every cell search identical in range, not whole column; how? 2
How can I get a postal code in a cell - from the internet? 2
?Validate Canadian Postal Code 5

Top