Index+Match with special characters

[farrusete]

Hi,

Im trying to get data from a different sheet table by matching IDs but im in troubles with values containing "~" character. For those the formula returns "#N/A"

=INDEX(Keywords[Description];MATCH([@Keyword];Keywords[Keyword];0))

How would you manage to get this working? (Id like to avoid manually replacing "~" character every time i update data tab.

Thank you in advance

 

[Somendra Misra]

@farrusete

Try using this formula,
=INDEX(Keywords[Description],MATCH([@Keyword],Keywords[Keyword],IF(ISNUMBER(SEARCH("=",[@Keyword])),0,-1)))

Regards,

 

[Debraj]

@farrusete

"~" & "*" are wildcard character..
To actually denote "~" & "*", you have to type Double time it.. like "~~" or "**", Excel will assume, you are not using "~" as wildcard, but as a character.

Try this..
=INDEX(Keywords[[#All],[Description]],MATCH(SUBSTITUTE(Tabla2[[#This Row],[Keyword]],"~","~~"),Keywords[[#All],[Keyword]],0))

use Substitute to convert single time "~" to Double time "~~"

=INDEX(Keywords[Description];MATCH(SUBSTITUTE([@Keyword],"~","~~");Keywords[Keyword];0))

 

[farrusete]

Thank you so much!