Forum Discussion
IT vs Facilites data
=IFERROR(FILTER($I$4:$J$11,$H$4:$H$11=C4),"")If you work with Excel 2019 or later you can try this formula.
=IFERROR(INDEX(I$4:I$7,MATCH($C4,$H$4:$H$7,0)),"")With older versions you can try INDEX and MATCH.
Thanks!
I had to make some alterations as my spreadsheet is a table. Once I converted to range, it worked.
The drawback is, the source sheet has repeated listings for multiple site codes. In this case, the result of the formula is "#SPILL!". Is there any way around that?
Nate
The best alternative should be Power Query however. In the attached file you can enter data into the blue dynamic tables. Then click in any cell of the green table and right-click with the mouse. Then select refresh.
OliverScheurich it works, thank you!
=IFERROR(BYROW(C4:C12,LAMBDA(row,TEXTJOIN(",",,FILTER(E16:F21,D16:D21=row)))),"")In this example all results are returned in the cell adjacent to the Site code cell. From here you can easily split the Office names and addresses into single cells with the TEXTSPLIT function if this is required.
