Sheet1 list names of workers Sheet3 list names of clients followed by names of the workers who worked with them.
I want it where if the workers name is in one of the following columns of sheet3 to show the clients name behind theirs on sheet1
For example
sheet 1
Sheet 1 (formula should go in cell B1)
| A | B | |
|---|---|---|
| 1 | Jhon | (f-x.) |
| 2 | Mark |
sheet 3
| A | B | C | |
|---|---|---|---|
| 1 | Andy | Jhon | Mark |
Desired output
Sheet 1 (formula in cell B1)
| A | B | |
|---|---|---|
| 1 | Jhon | Andy |
| 2 | Mark | Andy |
Sheet 3 (no changes)
| A | B | C | |
|---|---|---|---|
| 1 | Andy | Jhon | Mark |
Assuming the data in Sheet3!A1:E is like this:
| Client | Workers | |||
|---|---|---|---|---|
| Andy | Jhon | Mark A. | ||
| Alice | Mark A. | Sine | Jhon | Eve |
| Amanda | Eve | Mark A. | Mark B. | |
| Akira | Jill | Jhon | Mark A. |
Put this formula in cell Sheet1!B1:
=let(
names, byrow(Sheet3!B1:Z, lambda(row, "_" & join("_", row) & "_")),
map(tocol(A1:A, 1), lambda(name,
torow(filter(Sheet3!A1:A, search("_" & name & "_", names)))
))
)
...to get this:
| Worker | Clients | |||
|---|---|---|---|---|
| Jhon | Andy | Alice | Akira | |
| Mark A. | Andy | Alice | Amanda | Akira |
| Eve | Alice | Amanda | ||
| Jill | Akira | |||
| Sine | Alice | |||
| Mark B. | Amanda |