Forum Discussion
Solved
Combining IF statement
Good day all, I am trying to combine two IF statements but cant get it to work. It needs to find the date and the allocated values for that date and only then follow the calculation formula.
1. =IF(R1=Table615[Date], then follow the following formula
2. =IF(Q3=Table6[Kg],Table6[p/kg],IF(Q3>Table6[Kg],Table6[p/kg]-((Q3-Table6[Kg])*Table6[Penalty 1]),IF(Q3<Table6[Kg],Table6[p/kg]+((Table6[Kg]-Q3)*Table6[Penalty 1]))))
Here is a version that will work in Excel 2013:
=IFERROR(VLOOKUP($B$1, Table6, 3, FALSE)+(A3-VLOOKUP($B$1, Table6, 2, FALSE))*VLOOKUP($B$1, Table6, 4, FALSE), "Date Not Found")
Hi Janedb,
you are close to combine it together.You can try to use this formula to do that:
=IF(R1=Table615[Date], IF(Q3=Table6[Kg], Table6[p/kg], IF(Q3>Table6[Kg], Table6[p/kg]-((Q3-Table6[Kg])*Table6[Penalty 1]), IF(Q3<Table6[Kg], Table6[p/kg]+((Table6[Kg]-Q3)*Table6[Penalty 1])))), "Date not found!")This formula will first check if R1 matches any date in Table615[Date]. If it does, it will then follow the calculation formula you provided.
If it doesn't find a matching date, it will display "Date not found!"Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.If the post was useful in other ways, please consider giving it Like.
Kindest regards,
Leon Pavesic
- Nearly there but as soon as I add more than one date the formula does not work.
You have two tables, Table615 and Table6, where you would like to place the result?
Next, R1=Table615[Date] returns some array, within it you would like to place another array depends on condition. Afraid that doesn't work.
Perhaps you may share small sample file to illustrate the case?
SergeiBaklan Hi Sergei, please see the sample attached.
A3 and A4 contain #REF! because they refer to another workbook...
