Forum Discussion

Barak1100's avatar
Barak1100
Copper Contributor
Jul 12, 2023

If function

Hi, I want to make an IF function that the data is inputted from 3 different columns.

Column A is a combo box of text data.

Column B is a numeral numbers that has 3 different ranges.

Column C is a check box.

 

I want that column D show different options depends on all of the columns combinations, for example:

 

If column A shows apple, column B shows 2 and column C is checked it will show the words "cashier A" on column D.

 

If column A shows water, no matter the

other 2 columns, column D will show

the words "not allowed".

Formulas and Functions
  • H2O's avatar
    H2O
    Iron Contributor
    Jul 12, 2023
    To create an IF function that depends on three different columns, you need to use nested IF functions and logical operators. Here is a possible solution for your example:

    - In column D, enter this formula: =IF(A1="water","not allowed",IF(AND(A1="apple",B1=2,C1=TRUE),"cashier A","other option"))
    - This formula will first check if column A is "water". If yes, it will return "not allowed". If no, it will check if column A is "apple", column B is 2, and column C is checked. If yes, it will return "cashier A". If no, it will return "other option".
    - You can copy this formula down to the other cells in column D to apply it to the other rows.
    - You can also modify this formula to add more conditions and outcomes based on your needs.

    I hope this helps you create an IF function that works for your data.
    • mtarler's avatar
      mtarler
      Silver Contributor
      Jul 12, 2023
      Alternatively, IFS statement instead of nested IF. Also you can apply it to a range of inputs and have it 'SPILL' all the results using 1 formula:
      =IFS(A1:A100="water", "not allowed", (A1:A100="apple")*(B1:B100=2)*C1:C100, "cashier A", [next expression], [next result], .... , TRUE, "no result found")
      OR better yet define it as a table (Home -> 'Format as Table') and then use the Table references. So if Column A header is "Item", Column B is "Price" and Column C is "Taxable" then:
      =IFS([@Item]="water", "not allowed", ([@Item]="apple")*([@Price]=2)*[@Taxable], "cashier A", [next expression], [next result], .... , TRUE, "no result found")
      and this formula will auto populate each new line as you add to the table.

Resources