Find the Row of a String from a Collection of Columns
The formula works on Microsoft Excel for Mac version 16.36. Given: Problem: Kassandra is in which team? Formula: = INDEX ( RESULT_ARRAY , MATCH ( 1 , MMULT ( -- ( COLUMN_HAYSTACK = NEEDLE ), TRANSPOSE ( COLUMN ( COLUMN_HAYSTACK )^ 0 )), 0 )) Actual Application: = INDEX ( A3:A6 , MATCH ( 1 , MMULT ( -- ( B3:D6 = A7 ), TRANSPOSE ( COLUMN ( B3:D6 )^ 0 )),0)) Result: "Eagle"