VLOOKUP("Smith",$B$1:$C$3,2,FALSE) would result in Ben Smith's birth date 02/02/1980  but if you insert a column before column C the formula won't work without change and you cannot search for a person's first name easily. So you decide to use INDEX($C$1:$C$3,MATCH("Smith",$B$1:$B$3,FALSE)) instead.
There are at least 3 alternatives to document value and formula in cells B100 and C100 in parallel:
1. [Worst] You fill B100 and C100 manually. If any of these cells changes you have to adapt the corresponding cell manually, too. This is errorprone.
2. [Better] You insert the formula in cell C100 as text with a leading ‘. Define the name EVALC with the value =EVALUATE(INDIRECT("RC[+1]",FALSE)). Set cell B100 to =EVALC. Now B100 will show the result of formula C100. Unless you insert rows before row 3, of course.
3. [Best] You insert the formula in B100. Define the name GETCELLC with the value =GET.CELL(6,INDIRECT("RC[1]",FALSE)). Set C100 to =GETCELLC. Now C100 would show the formula used in B100. Even if you insert rows before row 3.
To show even array formulas you might want to define GETCELLC as: =REPT("{",GET.CELL(49,INDIRECT("RC[1]",)))&GET.CELL(6,INDIRECT("RC[1]",))&REPT("}",GET.CELL(49,INDIRECT("RC[1]",)))
An overview of many possible argument values of GET.CELL is shown here.
