SOLVED

# Combining IF statement

Brass Contributor

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

10 Replies

# Re: Combining IF statement

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?

# Re: Combining IF statement

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

# Re: Combining IF statement

@Sergei Baklan Hi Sergei, please see the sample attached.

# Re: Combining IF statement

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

# Re: Combining IF statement

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

# Re: Combining IF statement

@Hans Vogelaar Sorry, I forgot to remove the link.

# Re: Combining IF statement

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

# Re: Combining IF statement

I have Office 2013:(
best response confirmed by Janedb (Brass Contributor)
Solution

# Re: Combining IF statement

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

# Re: Combining IF statement

Awesome!!! Thank you.