Forum Discussion
IT vs Facilites data
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
=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.
