REFERENCE ... VLOOKUP ... OFFSET... MATCH?
 

Good afternoon

I have a situation where I need to assign a zone number to a order number
based on where the order is originating. In my situation I am dealing with
Canadian Zip Codes and they have a pattern like ANANAN (where N=number and
A=character)

In spreadsheet 1, my supplier gave me a list of zips like "from zip codes
starting to B20 to zip codes B2Z = Zone 1"
which translates into a table with 3 columns Start Zip-3 from, Start Zip-3
to and zone number

In spreadsheet 2, I have a list of all the orders and the exact zip codes.

What would be the best way for me to lookup the zone number in my
spreadsheet 2 and to insert it into a cell on the same row as my order
number? Any idea of the formula i need to write to get the zone number based
on the first 3 digits of my zip codes, check it in the spreadsheet 1 and
import it in my spreadsheet 2?

Thanks so much


Mk

REFERENCE ... VLOOKUP ... OFFSET... MATCH?
 

The Index/Match functions will give you what you want:
http://www.contextures.com/xlFunctions03.html


Regards,
Ryan--

--
RyGuy


"Michel Khennafi" wrote:

Good afternoon

I have a situation where I need to assign a zone number to a order number
based on where the order is originating. In my situation I am dealing with
Canadian Zip Codes and they have a pattern like ANANAN (where N=number and
A=character)

In spreadsheet 1, my supplier gave me a list of zips like "from zip codes
starting to B20 to zip codes B2Z = Zone 1"
which translates into a table with 3 columns Start Zip-3 from, Start Zip-3
to and zone number

In spreadsheet 2, I have a list of all the orders and the exact zip codes.

What would be the best way for me to lookup the zone number in my
spreadsheet 2 and to insert it into a cell on the same row as my order
number? Any idea of the formula i need to write to get the zone number based
on the first 3 digits of my zip codes, check it in the spreadsheet 1 and
import it in my spreadsheet 2?

Thanks so much


Mk