How to Remove Arrays and Apply formula into Each Cell-CodePudding

I have been using this formula since couple of days where it helps me alot to find the extact match from the raw data.

Earlier it was giving me an error #Value and ! so i used Substitue Function to remove these error. I need to fix this problem as well so i can remove subsctitute

I am basically looking to to apply this fomrula on each cell separalty rather than Array i tried but it was not working when i removed the arrays.

I have raw data from which i copy paste the relvant data into cells manually where Arrays disturb them.

=ARRAYFORMULA(SUBSTITUTE(SUBSTITUTE(TRIM(TRANSPOSE(QUERY(TRANSPOSE(UPPER(IFNA(REGEXEXTRACT(IFERROR(SPLIT(LOWER(B2:B), " "),""), LOWER("^"&TEXTJOIN("$|^", 1, 'Key words'!C1:C500)&"$")),""))),,999^99))),"#VALUE",""),"!",""))

If there is another Exact Match Function then please share. I would appreciate your help.

I hope to get some help. Thanks.

CodePudding user response：

use in C2:

={"State"; INDEX(TRIM(FLATTEN(QUERY(TRANSPOSE(
 IFERROR(REGEXEXTRACT(SPLIT(B3:B, ", ", 1)&"", 
 QUERY("×|^"&'Match Criteria'!B2:B100&"$|×",,9^9)))),,9^9))))}

use in D2:

={"Zip Code"; INDEX(IFNA(VLOOKUP(C3:C, 
 {'Match Criteria'!B2:B, 'Match Criteria'!A2:A}, 2, 0)))}