I am trying to create a function which match the exact value in the raw string if macthes then paste the value but here Lookup function is giving an wrong answer.

I know how to extract it using Left function but how to macth exact string like in VBA we use Criteria: "" & CellValue & "".

=LOOKUP(99^99,SEARCH($K$3:$K$97,A3),$K$3:$K$97)

This function is not matching exact string as you can see in attached picture.

REFERENCES:

• REGEXEXTRACT
• VLOOKUP
• IFERROR
• ARRAYFORMULA
• IFNA
• TO_TEXT

CodePudding user response：

I have developed this solution but it is extremly slow.

=OFFSET('Match Criteria'!$B$1,(SUMPRODUCT(--NOT(LEN(B3)=LEN(SUBSTITUTE(B3," "&'Match Criteria'!$B$2:$B&" ","")))*(ROW('Match Criteria'!$B$2:$B)-1))),)