From investigation on the internet I have determined how to do this for a simple problem. I am using the VBA function shown here.

Option Explicit
Function fxTextToFormulaUDF(text As String)
    Application.Volatile
    fxTextToFormulaUDF = Application.Caller.Parent.Evaluate(text)
End Function

I wish to create a formula which uses, as a portion of it, text from a cell. For a very simple problem this works fine. See this example:

   A         D            C          D       E  

1 Item Order_num
2 abc124 200
3 ‘XLOOKUP(A2,ID,
4
5 ID Description Quantity Cost Order_num
6 abc123 A 1 10 100
7 abc124 B 2 20 200
8 abc125 C 3 30 300

The formula in cell D2 is =fxTextToFormulaUDF(right(D3,len(D3)-1),D1).
With this one can change the value in cell D1 and the appropriate value is displayed in cell D2.

However, for a more complex XLOOKUP formula it doesn’t work. See the image included. Here the XLOOKUP is retrieving values from a file on disk. I’m getting a #REF! error. Any thoughts? Thanks in advance!

See above. Perhaps retrieving from the disk is the issue.