IT vs Facilites data

[NateWilcox]

Hello all. I have a list of IP ranges, site codes, and subnets. I need to cross reference those with a facilities list that contains friendly names, addresses, etc.

The only common reference I have is the site code. I need to reference my IT spreadsheet against a Facilities spreadsheet to fill columns with friendly names, addresses, etc.

How best to do this?

My sheet has:

IP address  | subnet        | site code   |

10.1.x.x       | 255.255.x.x | USLAX100 |

Facilities sheet has

site code   | Office Friendly name          | Address            |

USLAX100 | Los Angeles friendly office | 123 N South st |

I need to have my sheet pull the site code from the facilities sheet and fill in the office, friendly         name, etc. and fill the additional columns.

Ideas?

Thanks,

Nate

[#2]

@NateWilcox

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

[#3]

@OliverScheurich 

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

[#4]

@NateWilcox 

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

 

[#5]

@NateWilcox 

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.

[#6]

@OliverScheurich it works, thank you!