Forum Discussion
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...
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.
- 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.
- 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.- Thank you.
- As always, there are so many options in Excel. FYI - In some cases I prefer nested IF() instead of IFS(). There are some discussions about the benefits of each but basically if you are not using dynamic arrays and have complicated formulas that you do NOT want to get calculated then nested IF can be better. So for example
IF( simple case is true, "answer A", IF( complicated computationally taxing case,, "answer B", "answer C"))
is better than using IFS because IFS calculates all parts regardless while IF will not (as long as you don't put dynamic arrays inside).
