Forum Discussion
Looking for a number in a cell of a range and returning other cells values
I am trying to make an schedule for my group. I want to know who is going to be working by knowing the days each member is going to be off
Something like this:
Formula located on E4 returned B6,B7,B8 and B9 because cell above E4 can be found on C5:C9.
Obviously, in this example, I filled manually E4 to K4. Also, I wonder if Excel recognizes more than one number in a cell (as noted on column C5:C9).
Thank you.
5 Replies
Do I understand correctly that day numbers are sequential ones for the scheduled period, not weekday numbers? Thus we may have days off like 1, 11, 111, whatever. My guess is based on sample for person B.
- Thank you!
- =Textjoin(", ",1,Filter($B5:$B9,Iserror(Search(E3,$C5:$C9))))
=TEXTJOIN(",",,FILTER($B$5:$B$9,NOT(ISNUMBER(SEARCH(E3&E2,$C$5:$C$9)))))If i adapt the data in range C5:C9 from e.g. "1 2 3 4 5" to "1F 2Sa 3Su 4M 5Tu" the above formula seems to work in Office365 or 2021.
Thank you! OliverScheurich
