Search code examples
excelstringexcel-formulaformulapartial-matches

Excel String lookup within String

enter image description here

i have list of "Place of receipt", i need to lookup with another column with set of list that contains City names in Proper case/ Proper name.

i tried below formula but i did not found any luck

=(VLOOKUP(LOOKUP(2^15,SEARCH($I$1:$I$7,A2),$I$1:$I$7),$I$1:$I$7,1,0))

it gives me result when the first word from cell matches with another one, but it will throw error when "place of receipt" has different word after first word,

for example "BRADFORD GB" will not get "Bradford, WYK" from city list as shown in above image.

Solution

  • i came across a solution to find string before comma and space, if the first word matches it will check the word (+3) which is after the comma. the result is now expected.

    The formula i used :

    =IFERROR(VLOOKUP(LOOKUP(2^15,SEARCH((LEFT(A2,(FIND(",",A2,1)+3))),$I$1:$I$8,1),$I$1:$I$8),$I$1:$I$8,1,0),(VLOOKUP(LOOKUP(2^15,SEARCH((LEFT(A2,(FIND(" ",A2,1)-1))),$I$1:$I$8,1),$I$1:$I$8),$I$1:$I$8,1,0)))

    Solution

    enter image description here