google-sheetsreplacesubstitution

Google Sheets - replace the exact text/number with a different one ignoring other matching parts


I need a formula that automatically replaces a certain text or number in Google Sheets (places the result in a different column), but only if this the exact match.

I'm trying to add a formula that will automatically change a certain number or text (and place it to the column D) when someone adds it to the column A.

I've tried using =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B3;"99";"8");"199";"15");"9999";"700"), but it returns 9999 as 88 (two times 99) instead of 700 and this is true for any text values as well. I've also tried using =REGEXMATCH / REGEXREPLACE, but it doesn't work with OR operator for some reason and using AND just adds matching values on top of each other (like getting 8700 instead of just 700 when trying to replace 9999 in this example). This is a nonlinear dependence, so simply multiplying/dividing doesn't work in this case.

I'm trying to solve this issue with an easy to follow and edit formula, so I'd rather avoid using scripts if possible (I'm afraid it can't be difficult for me to edit it later and explain how it works for other non tech savvy users). Any help is highly appreciated!

old data text data new data (what I get) desired outcome
99 99 8 8
199 199 18 15
9999 9999 88 700

Solution

  • Create a lookup_table at some spot(as shown in screenshot) which lists all the substitutions you wish to accommodate & then use the xlookup as below:

    =arrayformula(xlookup(A2:A,F:F,G:G,))
    

    enter image description here