I have two sheets, one with computer information and another with processors. I want to add a column in the processor sheet that lists the computers that use those processor even if I change the order of any of the sheet.
This is my Google Sheet exemple
I have found that this formula works only if the same row contains the data, not the whole column, since it gives off #VALUE errors for every E that is not contained in the same C row.
=ARRAYFORMULA(IF(SEARCH(E2:E;C2:C); JOIN(", ";B2:B); ""))
It also adds ALL the computers and not only the ones that have the same processors. In the screenshot it is CONCATENER but I found JOIN worked better.
Any suggestion or idea would be great. I have already had to change how the processors are written in both sheets so they would be easier to search by the formula. The point is to know what server I can use which processor without having to go between the two sheets all the time.
EDIT: Using this formula gives me what I want only in one cell but only adds one string of text even if there should be more.
=ARRAYFORMULA(JOIN(""; SI(E2=$C$2:$C; $B$2:$B & ","; "")))
I think I understand what you want. Put this in E2:
=unique(transpose(split(join(",";C:C)&join(",";C:C);",")))
Tis will create a list of unique processors in column E. Then put this in F2 and drag the formula down column F:
=if(E2<>"";join(", ";transpose(ifError(filter(B:B;ifError(search(E2;C:C);false)))));"")
This will join the list of computers in the cell in column F next to the processor.