I need a formula that add the nurses name from column F into Colum D if they have that room (G3 to K7)
For example: Nurse A currently have room 2101, 2108, 2155, 2165 so her name should be displayed in the specific room row.
Room | Patient Name/Age | Code | Nurse |
---|---|---|---|
2101 | Full | ||
2102 | Full | ||
2103 | Full | ||
2104 | FC | ||
2105 | |||
2106 | Full | ||
2107 | |||
2108 | Full | ||
2109 | Full | ||
2110 | Full | ||
2151 | Full | ||
2152 | |||
2153 | Full | ||
2154 | Full | ||
2155 | FC | ||
2156 | Full | ||
2157 | Full | ||
2161 | Full | ||
2162 | Full | ||
2163 | |||
2164 |
Nurse | Room 1 | Room 2 | Room 3 | Room 4 | Room 5 |
---|---|---|---|---|---|
A | 2101 | 2108 | 2155 | 2165 | |
B | 2102 | 2109 | 2156 | 2166 | |
C | 2103 | 2110 | 2157 | 2167 | |
D | 2104 | 2151 | 2161 | 2168 | |
E | 2105 | 2152 | 2162 | 2169 | |
F | 2106 | 2153 | 2163 | 2170 | |
G | 2107 | 2154 | 2164 | 2170 |
I used the following =IF(A3=H3,F3,"")
and it did the trick but just for one cell. I tried multiple cells and ended up breaking the sheet.
Try using anyone of the following formulas per your preferences:
• Using IFS()
+ TOCOL()
=TOCOL(IFS(A3=G$3:K$9,F$3:F$9),2)
• Or, Using REPT()
+ CONCAT()
=CONCAT(REPT(F$3:F$9,G$3:K$9=A3))
• Using MAP()
+ TOCOL()
+ IFS()
to return for the whole range as an array output.
=MAP(A3:A23,LAMBDA(x,TOCOL(IFS(G3:K9=x,F3:F9),2)))
• Or, Older Versions -- using INDEX()
+AGGREGATE()
+IFERROR()
:
=IFERROR(INDEX(F$3:F$9,AGGREGATE(15,7,(ROW(F$3:F$9)-ROW(F$3)+1)/(G$3:K$9=A3),1)),"")
• Or, ETA LAMBDA()
using FILTER()
+BYROW()
+OR()
=FILTER(F$3:F$9,BYROW(G$3:K$9=A3,OR),"")
• Or, Using MAP()
+ETA LAMBDA()
--> BYROW()
+FILTER()
+OR()
=MAP(A3:A23,LAMBDA(x,FILTER(F3:F9,BYROW(G3:K9=x,OR),"")))
• Or Using MMULT()
+ FILTER()
=FILTER(F$3:F$9,MMULT(N(G$3:K$9=A3),TOCOL(--RIGHT(G$2:K$2))^0),"")
Shared the possible ways, as one can return the output in many ways, the above outlined solutions, gives an idea about the concept on arrays as well helps to learn.