VLOOKUP returns #N/A when searching for the ~ symbol.
D Dave Peterson Dec 20, 2004 #4 And you may not get the answer you want when your data has asterisks or question marks, too. =VLOOKUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"~","~~"),"?","~?"),"*","~*"), Sheet2!$A:$B,2,FALSE) would be one way to avoid all three problems.
And you may not get the answer you want when your data has asterisks or question marks, too. =VLOOKUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"~","~~"),"?","~?"),"*","~*"), Sheet2!$A:$B,2,FALSE) would be one way to avoid all three problems.
