How to do VLOOKUP in Powershell (or at least an equivalent!)

First of all apologies for the title which is a bit click baity – it’s because I spent a lot of time googling for this and couldn’t find what I needed, that had been done in the way I wanted it doing. This is like a digital notebook for me to come back to as a reminder!

The problem…

What I wanted to achieve is a straight forward way of taking two Powershell system array objects and combining them together into a new system.array in a similar way to how Excel does a VLOOKUP against two data series. So take $array1, match it against $array2 and produce $array3 that includes all the information from the original two arrays, combined together into a single object.

The solution…

Here’s how I did it:

[cc lang="powershell"]
$Combined = @()
foreach ($i in $inputfile) {
	foreach ($iter in $allusers) {
		if ($i.username -match $iter.samaccountname) {
			write-host "Match found between $($i.username) and $($iter.samaccountname)"
			$hash = @{
				Username   		=   $i.username
				NewDIR			=	$i.destination
				SamAccountName 	=   $iter.samaccountname
				CurrentDIR		=	$iter.homedirectory
			}
			$Build = New-Object PSObject -Property $hash
			$Total = $Total + 1
			$Combined += $Build
			}
		}
	}
Write-Host "Processed $Total records"
[/cc]