excelexcel-formula

How can I use index and match two way lookup

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

Sheet Picture

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.

Solution

  • Try using anyone of the following formulas per your preferences:

    enter image description here

    • 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.