Does LOOKUP() perform an exact match?

[JoeMcFadden]

Sorry if I'm missing this somewhere in the help docs (I have looked), but can I check if LOOKUP() should perform an exact match against the column it is searching, or will it match if the search text is contained somewhere inside the column value? 

For example, will a lookup for a phone number "447884323239" only work if the column has just that exact value in it and nothing else, or would it also work if the number had a leading '+'?

Thanks

[1minManager · Accepted solution]

Its an exact but not case sensitive match, the same as Select().  So the following will all be seen as different

• 447884323239
• +447884323239
• 07884323239
• 07884 323239

Sometimes you have run a seperate process to 'fix' the column so that the following match formula is consistent.  Looks like your using a UK mobile number so here's one of my formulas for fixing these:

IFS(
   LEFT([Tel],1)="0",[TEL],
   LEFT([Tel],2)="44",CONCATENATE("0",Substitute([Tel],"44","")),
   LEFT([Tel],3)="+44",CONCATENATE("0",Substitute([Tel],"44","")),
   LEFT([Tel],1)="7",CONCATENATE("0",[Tel]),
   LEFT([Tel],1)="1",CONCATENATE("0",[Tel]),
   LEFT([Tel],1)="2",CONCATENATE("0",[Tel]),
)

We do this because the number comes from someone filling in Google Form with no formating restrictions.

Simon, 1minManager.com 

[JoeMcFadden]

Thanks Simon for confirming how the match works, and thank you for sharing your fix formula as well - very helpful!