Most optimized way to search for an Object in an ArrayList-CodePudding

I have a list of IPs (list "X") for which I'm trying to find the domain name. I also have an Excel file containing 170 000 entries of IP with the corresponding domain name.

So first I import the Excel file: $dnsEntries = Import-Excel -Path '.\file.xlsx' -WorksheetName "list"

And then for each IP in list "X", I try to find it in $dnsEntries, so I can get the domain name:

foreach($IP in $IPs) {
    $DN=($dnsEntries.Where({$_."Address" -like $IP})).Name
}

Unfortunately, this takes at least 2 seconds for each IP, and I have 5000 , so this takes a loong time. What bothers me is that Excel can find ALL 5000 corresponding domain names in less than 3 seconds with a simple formula. I understand that I could never come this close with PowerShell, but how the difference could be so big?

Is there any way I could speed up the search?

Thanks a lot in advance!

Edit: Here is a reproducible example. Some IPs won't be matched.

$IPs = @("10.10.10.1","10.10.10.5","10.10.10.66","10.10.10.99","10.10.10.235","10.10.10.73","10.10.10.98","10.10.10.56")
$dnsEntries = @(
    @{Address="10.10.10.73";Name="ip1.local"},
    @{Address="10.10.10.98";Name="ip2.local"},
    @{Address="10.10.10.1";Name="ip3.local"},
    @{Address="10.10.10.56";Name="ip4.local"},
    @{Address="10.10.10.235";Name="ip5.local"},
    @{Address="10.10.10.5";Name="ip6.local"},
    @{Address="10.10.10.11";Name="ip7.local"},
    @{Address="10.10.10.81";Name="ip8.local"},
    @{Address="10.10.10.2";Name="ip9.local"}
)

foreach($IP in $IPs) {
    $DN=($dnsEntries.Where({$_."Address" -like $IP})).Name
    Write-Host $DN
}

CodePudding user response：

As stated in one of the comments, I converted the $dnsEntries from an ArrayList to a Hashtable with the following code:

$import = Import-Excel -Path '.\file.xlsx' -WorksheetName "list"
$dnsEntries = @{}

foreach($entry in $import){
    $dnsEntries.Add($entry.Address,$entry.Name)
}

foreach($IP in $IPs) {
    $DN=$dnsEntries[$IP]
    Write-Host $DN
}

It still takes a long time to convert it, but then looking for a domain name for an IP is almost instant! So in the end it saves a lot of time.

Thanks to all of you for your advice!