extract city using city list lookup

--------------------------------------------------------------------------------

Please Login or Register  to view this content.

I tried this formula, but run into a little trouble.

Say a record is like this:

E2
255 SIMCOE ST N APT 505 OSHAWA L1G 4T4, ONTARIO

Oshawa is the tarket, but both Oshawa and Simcoe appear in the lookup list. Because Simcoe comes last in the a-z, it gets picked.

Is there a way to search from the right side a certian number of spaces, or pick the first right side match, or search with in some limits before the comma?

Thanks
Terry

You can employ the RIGHT FUNCTION...

=LOOKUP(2^15,SEARCH($A$2:$A$3424,RIGHT(E2,20)),$A$2:$A$3424)

which means only look in the right 20 chars of E2 (you can increase/decrease), but that might or might not still be too limiting... I don't know...