How can I change the formula of one cell based on the value in a different cell?

Question

COLUMN D: Dropdown list of values

COLUMN E: IF function returning different values depending on COLUMN D contents

I have the functionality described above working properly. My problem is that I want the IF function in COLUMN E to return a working formula rather than a value.

Right now the IF function only returns the actual formula text rather than using the formula itself within the cell. I'm assuming that this is because the IF function can only provide values, not formulas, as outputs.

Is there a better approach or function I can use to insert a completely different, operational formula into COLUMN E depending on the value listed in COLUMN D?

Your help is appreciated.

EDIT: An example of my problem can be found on the following sheet:

https://docs.google.com/spreadsheets/d/1m_-rLniq-il7jL0ilWZWxh4cYq65DW2V2uQwtFgQLLA/edit?usp=sharing

Answer 1

• paste in B2 cell:

  =ARRAYFORMULA(IFERROR(VLOOKUP(A2:A, data!$A:$C, {2, 3}, 0)))

• paste in E2 cell:

  =ARRAYFORMULA(IFERROR(VLOOKUP(A2:A&B2:B&D2:D, 
   TRIM(SPLIT(TRANSPOSE(SPLIT(TRIM(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
   IF(LEN(data!A2:A), "♦"&data!A2:A&data!B2:B&
   IF(LEN(data!D2:G), REGEXEXTRACT(data!D1:G1, "^..")&"♠"&data!D2:G, ), ))
   ,,999^99)),,999^99)), "♦")), "♠")), 2, 0)*1))

  

• spreadsheet demo