Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
is it vlookup, match, offset? | Excel Worksheet Functions | |||
Vlookup/match/offset over multiple columns of lable | Excel Discussion (Misc queries) | |||
Which to use - if, vlookup, match, index, offset, vba? | Excel Discussion (Misc queries) | |||
match data to reference then vlookup | Excel Discussion (Misc queries) | |||
VLOOKUP, OFFSET, MATCH PROBLEM, HELP? | Excel Worksheet Functions |