FORMULA Q: IF AND OR

mistone

I'm trying to write this formula using index and match to reference sheets. The indexed columns are checkbox columns. 0 = not checked 1 = checked

IF INDEX & MATCH A = 1, Then "ORDER"

IF INDEX & MATCH A = 1 AND OR INDEX & MATCH B = 1, INDEX & MATCH C = 1, Then "ORDER & EMAIL"

IF INDEX & MATCH A = 0 AND OR INDEX & MATCH B = 1, INDEX & MATCH C = 1, Then "EMAIL"

Otherwise "NONE"

I know this is complete, but it is as far I as I could go before getting completely stuck. Any guidance on getting started would be much appreciated!

=IF(INDEX({3.0 IR Sheet - Panera Col}, MATCH(Itinerary@row, {3.0 IR Sheet - Itinerary Col}, 0)) = 1, "ORDER, OR(INDEX({3.0 IR Sheet - Logi Email Col}, MATCH(Itinerary@row, {3.0 IR Sheet - Itinerary Col}, 0)) = 1, INDEX({3.0 MLR Sheet - Pre-Dep Email C.}, MATCH([Meeting location]@row, {3.0 MLR Sheet - MEET Loc Col}, 0)) = 1), "PANERA & EMAIL")

Jeff Reisman

@mistone You are close, let me help with the syntax and logic.

The IF function works from left to right, and it stops as soon as it hits a true logical expression (or combo of logical expressions.) So if Index & Match A = 1, it's going to stop there and set the cell value to "ORDER." So that IF needs to go at the end of the train of nested IFs.

Next, you can embed an OR statement inside and AND within an IF! (😵). The logic is: IF A is true, and either B or C is true, then true, otherwise, false. The syntax is: IF(AND(logical expression 1, OR(logical expression 2, logical expression 3)), value if true, value if false)

I've indicated the first IF in BOLD, the second IF not bold, and the third IF in BOLD.

=IF(AND(INDEX({3.0 IR Sheet - Panera Col}, MATCH(Itinerary@row, {3.0 IR Sheet - Itinerary Col}, 0)) = 1, OR(INDEX({3.0 IR Sheet - Logi Email Col}, MATCH(Itinerary@row, {3.0 IR Sheet - Itinerary Col}, 0)) = 1, INDEX({3.0 MLR Sheet - Pre-Dep Email C.}, MATCH([Meeting location]@row, {3.0 MLR Sheet - MEET Loc Col}, 0)) = 1)), "ORDER & EMAIL", IF(AND(INDEX({3.0 IR Sheet - Panera Col}, MATCH(Itinerary@row, {3.0 IR Sheet - Itinerary Col}, 0)) = 0, OR(INDEX({3.0 IR Sheet - Logi Email Col}, MATCH(Itinerary@row, {3.0 IR Sheet - Itinerary Col}, 0)) = 1, INDEX({3.0 MLR Sheet - Pre-Dep Email C.}, MATCH([Meeting location]@row, {3.0 MLR Sheet - MEET Loc Col}, 0)) = 1)), "EMAIL", IF(INDEX({3.0 IR Sheet - Panera Col}, MATCH(Itinerary@row, {3.0 IR Sheet - Itinerary Col}, 0)) = 1, "ORDER", "NONE")))

Jeff Reisman

@mistone You are close, let me help with the syntax and logic.

The IF function works from left to right, and it stops as soon as it hits a true logical expression (or combo of logical expressions.) So if Index & Match A = 1, it's going to stop there and set the cell value to "ORDER." So that IF needs to go at the end of the train of nested IFs.

Next, you can embed an OR statement inside and AND within an IF! (😵). The logic is: IF A is true, and either B or C is true, then true, otherwise, false. The syntax is: IF(AND(logical expression 1, OR(logical expression 2, logical expression 3)), value if true, value if false)

I've indicated the first IF in BOLD, the second IF not bold, and the third IF in BOLD.

=IF(AND(INDEX({3.0 IR Sheet - Panera Col}, MATCH(Itinerary@row, {3.0 IR Sheet - Itinerary Col}, 0)) = 1, OR(INDEX({3.0 IR Sheet - Logi Email Col}, MATCH(Itinerary@row, {3.0 IR Sheet - Itinerary Col}, 0)) = 1, INDEX({3.0 MLR Sheet - Pre-Dep Email C.}, MATCH([Meeting location]@row, {3.0 MLR Sheet - MEET Loc Col}, 0)) = 1)), "ORDER & EMAIL", IF(AND(INDEX({3.0 IR Sheet - Panera Col}, MATCH(Itinerary@row, {3.0 IR Sheet - Itinerary Col}, 0)) = 0, OR(INDEX({3.0 IR Sheet - Logi Email Col}, MATCH(Itinerary@row, {3.0 IR Sheet - Itinerary Col}, 0)) = 1, INDEX({3.0 MLR Sheet - Pre-Dep Email C.}, MATCH([Meeting location]@row, {3.0 MLR Sheet - MEET Loc Col}, 0)) = 1)), "EMAIL", IF(INDEX({3.0 IR Sheet - Panera Col}, MATCH(Itinerary@row, {3.0 IR Sheet - Itinerary Col}, 0)) = 1, "ORDER", "NONE")))

mistone

@Jeff Reisman - Thank you. This works! The 1st IF bold 2nd not bold and 3rd bold formatting helps me understand the syntax. I'm still learning how to combine and nest functions together. Cheers!