Question

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 · Accepted Answer

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