MATCH() 'Value Not Available Error'

[fboehlandt]

Hi everyone

not the first time I have had issues with MATCH(), this function s*cks! Anyways, I expect there might be a workaround. I have a longer alpha-numerical string that I would like to match against a list of values (see example below):

20150123~1.43~Prem F/R 230115~20140812


20150123~1.43~Prem F/R 230115~20140812
20160201~40.11~GRT F/R 010216~20140812
20160414~15.79~SBK F/R 140416~20140812
20141127~86.85~SBK F/R 271114~20140812
20160104~7.2~ASA F/R 040116~20140812
20160410~177.98~Comm Str7 F/R 100416~20140812
20161117~18.47~FSR F/R 171116~20140812
20141120~48.46~FSR F/R 201114~20140812
20141202~88.18~FSR F/R 021214~20140812

I can manually confirm that the value I seek is in the first position. However, using the MATCH function I get the 'Value Not Available' Error. I copied everything from my spreadsheet as is to confirm there are no empty spaces affecting char length etc.

If I set match type = 1 it provides an answer but it is not what I'm looking for.

Any input is greatly appreciated

Regards


[TABLE="width: 315"]
<tbody>[TR]
[TD="width: 420, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 420"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Andrew Poulsom]

The tilde is a wildcard character so you need to escape it with another tilde. Example:

=MATCH(SUBSTITUTE(A1,"~","~~"),A3:A11,FALSE)

 

[fboehlandt]

Thanks Andrew, you are correct.