EXCEL FORMULA FUNCTIONS

LAMBDA can be used to run like a loop macro, but in a formula function instead.

For this example, the formula will look at strings with some incorrectly typed words, and see if they can be corrected by looping through a two-columnn table of before and after words to be substituited with.

=LAMBDA(T,B,A,IF(B="",T,Defined_Name(SUBSTITUTE(T,B,A,OFFSET(A,1,0),OFFSET(B,1,0))))

1) T,B,A, can be any letter, but in this case they stand for T=Text string, B=Before word, A=After Word. IF(B="",T... This is to end the loop.

2) Defined_Name (the whole formula will be the Defined_Name)

3) SUBSTITUTE(T,B,A, The loop will search in the "Text string", find the "Before" word, substitute it with the "After" word.

4) OFFSET(B,1,0... The B is the starting point of reference; 1 row down; 0 Column movement.

5) OFFSET(A,1,0... The B is the starting point of reference; 1 row down; 0 Column movement.