Compare a string with a column of strings and return matching ID Excel

Question

Excel Sheet 1

COLUMN A                       COLUMN B 
zone                           id_zone
Afognak Lake, AK    
Granite Mountain, AK    
Lik, AK 
Little Squaw, AK    
Kizhuyak, AK    
Klawock, AK 
Elizabeth Island, AK    
Homer, AK   
Hudson, NY  
Hudson, NY  
Peach Springs, AZ   
Peach Springs, AZ   
Peach Springs, AZ   
Peach Springs, AZ   
Blairstown, NJ
... 

Excel Sheet 2

Column A   Column B
id_zone    name_zone
30001      Afognak Lake, AK
30003      Granite Mountain, AK
30004      Lik, AK
30005      Little Squaw, AK
30006      Kizhuyak, AK
30007      Klawock, AK
30008      Elizabeth Island, AK
30009      Homer, AK
30010      Hudson, NY
30011      Peach Springs, AZ
30012      Blairstown, NJ
30013      Crosbyton, TX
30015      Deadmans Bay, AK
30016      Hallo Bay, AK
30017      Red Lake, AK
30018      Shell Lake, AK
30019      Kougarok, AK
30020      Selawik, AK
30021      Pillar Bay, AK
30022      Johnstone Point, AK
30023      Tikchik Lodge, AK
30024      Bell Creek, AK
...

How can I compare each string in sheet 1 with an array of strings in sheet2 and return the matching ID? I have been using lookup but it returns 75% the wrong ID.

Answer 1

Under id_zone in B1 out this formula and fill down.

=iferror(index('sheet2'!a:a, match(a2, 'sheet2'!b:b, 0)), "no matching id")

VLOOKUP always finds its match in the first column of the lookup range. You want to match in the second column and return the first so an INDEX/MATCH pair is more appropriate.