I need A NESTED FUNCTION does the next: 1- search in first sheet "case1" in coloum "c" about this value "mo" if it found it return this text "case1", if not doesn't return any thing(pic1) 2-does the same function in second and thrid sheets:(pic2) search in second sheet "case2" in coloum "c" about this value "mo" if it found it return this text "case2", if not doesn't return any thing// search in third sheet "case3" in coloum "c" about this value "mo" if it found it return this text "case3", if not doesn't return any thing 3- I want with it A TEXT FUNCTION collect the results beside each other (pic3)enter image description here

CodePudding user response：

This should work

=index(textjoin(" ",1,if(isna({match("mo",case1!C:C,0),match("mo",case2!C:C,0),match("mo",case3!C:C,0)}),,"case"&sequence(1,3))))

Update:

=index(textjoin(" ",1,if(isna({match("mo",case1!C:C,0),match("mo",above!C:C,0),match("mo",below!C:C,0)}),,{"case 1","above","below"})))