In cell D5:
=IF(VLOOKUP(C5,'store1'!$F$4:$F$29,1)=C5,"store1","")&(IF(VLOOKUP(C5,'store2'!$F$4:$F$29,1)=C5,"\store2","")&(IF(VLOOKUP(C5,'store3'!$F$4:$F$29,1)=C5,"\store3","")&(IF(VLOOKUP(C5,'store4'!$F$4:$F$29,1)=C5,"\store4","")&(IF(VLOOKUP(C5,'store5'!$F$4:$F$29,1)=C5,"\store5","")&(IF(VLOOKUP(C5,'store6'!$F$4:$F$29,1)=C5,"\store6","")&(IF(VLOOKUP(C5,'store7'!$F$4:$F$29,1)=C5,"\store7","")&(IF(VLOOKUP(C5,'store8'!$F$4:$F$29,1)=C5,"\store8","")&(IF(VLOOKUP(C5,'store9'!$F$4:$F$29,1)=C5,"\store9","")&(IF(VLOOKUP(C5,'store10'!$F$4:$F$29,1)=C5,"\store10",""))))))))))
I have ten store sheets with working employees name and one "all store employees name" sheet. Sometimes a few employees work in two stores and swipe with store1 to store2 or store3 or other.
What I want is if some employee worked in more then one store then store (ie sheet name) number show in 'all store employees name' sheet.
Example: "all store employees name" c5 to c30 is A to Z alphabet and f4 to f29 A to Z alphabet in all store.
The formula works but not properly, A to M show #N/A.
If the employee is not found in any store, that will be an error for that store. The most efficient method of looking for a value to simply exist is the MATCH function (even more efficient than COUNTIF) but you need to check if MATCH is returning a row number when found or an #N/A error where no match was found. The ISNUMBER function can determine that.
=MID(IF(ISNUMBER(MATCH(C5, store1!$F$4:$F$29, 0)), "\store1", "")
&IF(ISNUMBER(MATCH(C5, store2!$F$4:$F$29, 0)), "\store2", "")
&IF(ISNUMBER(MATCH(C5, store3!$F$4:$F$29, 0)), "\store3", "")
&IF(ISNUMBER(MATCH(C5, store4!$F$4:$F$29, 0)), "\store4", "")
&IF(ISNUMBER(MATCH(C5, store5!$F$4:$F$29, 0)), "\store5", "")
&IF(ISNUMBER(MATCH(C5, store6!$F$4:$F$29, 0)), "\store6", "")
&IF(ISNUMBER(MATCH(C5, store7!$F$4:$F$29, 0)), "\store7", "")
&IF(ISNUMBER(MATCH(C5, store8!$F$4:$F$29, 0)), "\store8", "")
&IF(ISNUMBER(MATCH(C5, store9!$F$4:$F$29, 0)), "\store9", "")
&IF(ISNUMBER(MATCH(C5, store10!$F$4:$F$29, 0)), "\store10", ""), 2, 99)
I've added the backslash to all store values and trimmed off the first backslash from the result no matter which store supplied it.
You can keep the 'whitespace' (e.g. linefeeds and spaces) in the formula to help you make more sense of it once it is in the worksheet's formula bar. This formula may look complicated but it actually does much less work than even the most rudimentary array formula.