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.

score 2 · Accepted Answer · 2018-10-27 14:37:58Z

2

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.

edited Oct 27, 2018 at 14:37

answered Oct 27, 2018 at 12:55

user4039065

Sign up to request clarification or add additional context in comments.

1 Comment

CodeOnce Over a year ago

Used that command, but it returns the name not the ID. On the positive side, it works xD, but doesn't give the value I wanted

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related