1

COLUMN D: Dropdown list of values

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

0

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.

0

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

  • 1
    share a copy of your sheet – user0 Jul 8 at 13:28
  • 1
    what is your formula? – user0 Jul 8 at 13:33
  • Hi, thanks for your reply. Here's an example: drive.google.com/… Columns B and C dynamically update depending on what's selected in Column A. I accomplish that by using VLOOKUP to pull data from specific columns in the data sheet. But there are multiple shipping rates, so when the "Shipping To" value is changed, I want to automatically change the adjacent formula in Column E. Using the IF function doesn't provide an operational formula -- just plaintext. I want a comparable function that actually changes the active formula. – headroom Jul 8 at 15:47
0
  • 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))

    0

  • spreadsheet demo

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.