Combining IF statement

[Janedb]

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]))))

[#2]

@Janedb 

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?

[#3]

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

[#4]

@SergeiBaklan Hi Sergei, please see the sample attached.

[#5]

Nearly there but as soon as I add more than one date the formula does not work.

[#6]

@Janedb 

A3 and A4 contain #REF! because they refer to another workbook...

[#7]

@HansVogelaar Sorry, I forgot to remove the link.

[#8]

@Janedb 

If you have Microsoft 365 or Office 2021, the attached version should work.

[#9]

I have Office 2013:(

[#10 · Accepted solution]

@Janedb 

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")

[#11]

Awesome!!! Thank you.